Re: PLEASE GOD HELP US! - Mailing list pgsql-admin

I don't think ORDER BY X=Y will use an index even if casted.  I may be wrong.
We're still using 7.3.5.



Joshua D. Drake wrote:
>>         SQL:  SELECT * FROM thread_listing AS t ORDER BY t.status=5
>> DESC,t.lastreply desc LIMIT 25 OFFSET 0
>>         Num Rows:    25
>>         Affected Rows:    0
>>         Exec Time:  9.1602659225464
>
>
> Is t.status a big or small int? You might need to cast it... This also
> goes for the other queries below. What are the explains for these queries?
>
> explain SELECT * FROM thread_listing AS t ORDER BY t.status=5
> DESC,t.lastreply desc LIMIT 25 OFFSET 0
>
> When was the last time you ran Vacuum, Analyze?
>
> Sincerely,
>
> Joshua D. Drake
>
>
>>
>>         SQL:  SELECT * FROM thread_categories WHERE parentcategoryid=0
>> AND
>> threadcategoryid<>0 ORDER BY orderid ASC
>>         Num Rows:    4
>>         Affected Rows:    0
>>         Exec Time:  0.81906294822693
>>
>>         SQL:  SELECT * FROM thread_categories WHERE parentcategoryid=1
>> AND
>> promoted=true ORDER BY orderid ASC
>>         Num Rows:    9
>>         Affected Rows:    0
>>         Exec Time:  0.0021350383758545
>>
>>         SQL:  SELECT * FROM thread_categories WHERE parentcategoryid=2
>> AND
>> promoted=true ORDER BY orderid ASC
>>         Num Rows:    5
>>         Affected Rows:    0
>>         Exec Time:  0.0019958019256592
>>
>>         SQL:  SELECT * FROM thread_categories WHERE parentcategoryid=3
>> AND
>> promoted=true ORDER BY orderid ASC
>>         Num Rows:    4
>>         Affected Rows:    0
>>         Exec Time:  0.0019819736480713
>>
>>         SQL:  SELECT * FROM thread_categories WHERE parentcategoryid=4
>> AND
>> promoted=true ORDER BY orderid ASC
>>         Num Rows:    5
>>         Affected Rows:    0
>>         Exec Time:  0.0021347999572754
>>
>>         SQL:  SELECT userid,username FROM users WHERE userid IN (select *
>> from buddynetwork(0,2)) ORDER BY username ASC
>>         Num Rows:    1
>>         Exec Time:  0.0011849403381348
>>
>>
>>
>>  # PGSQL Version 7.4.2
>>  # -----------------------------
>>  # PostgreSQL configuration file
>>  # -----------------------------
>>
>> #---------------------------------------------------------------------------
>>
>>  # CONNECTIONS AND AUTHENTICATION
>>
>> #---------------------------------------------------------------------------
>>
>>  # - Connection Settings -
>>  tcpip_socket = true
>>  max_connections = 50
>>  #superuser_reserved_connections = 2
>>  port = 5432
>>  #unix_socket_directory = ''
>>  #unix_socket_group = ''
>>  #unix_socket_permissions = 0777
>>  #virtual_host = ''
>>  #rendezvous_name = ''
>>  # - Security & Authentication -
>>  #authentication_timeout = 60
>>  ssl = true
>>  password_encryption = true
>>  #krb_server_keyfile = ''
>>  #db_user_namespace = false
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # RESOURCE USAGE (except WAL)
>>
>> #---------------------------------------------------------------------------
>>
>>  # - Memory -
>>  shared_buffers = 8192
>>  sort_mem = 8192
>>  vacuum_mem = 127072
>>
>>  # - Free Space Map -
>>  max_fsm_pages = 50000           # min max_fsm_relations*16, 6 bytes each
>>  max_fsm_relations = 1000        # min 100, ~50 bytes each
>>  # - Kernel Resource Usage -
>>  max_files_per_process = 3052    # min 25
>>  #preload_libraries = ''
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # WRITE AHEAD LOG
>>
>> #---------------------------------------------------------------------------
>>
>>  # - Settings -
>>  fsync = true                    # turns forced synchronization on or off
>>  #wal_sync_method = fsync
>>  wal_buffers = 8192              # min 4, 8KB each
>>
>>  # - Checkpoints -
>>  #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
>>  #checkpoint_timeout = 300       # range 30-3600, in seconds
>>  #checkpoint_warning = 30        # 0 is off, in seconds
>>  #commit_delay = 0               # range 0-100000, in microseconds
>>  #commit_siblings = 5            # range 1-1000
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # QUERY TUNING
>>
>> #---------------------------------------------------------------------------
>>
>>
>>  # - Planner Method Enabling -
>>  #enable_hashagg = true
>>  #enable_hashjoin = true
>>  #enable_indexscan = true
>>  #enable_mergejoin = true
>>  #enable_nestloop = true
>>  enable_seqscan = false
>>  #enable_sort = true
>>  #enable_tidscan = true
>>  # - Planner Cost Constants -
>>  effective_cache_size = 131072   # typically 8KB each
>>  random_page_cost = 4            # units are one sequential page fetch
>> cost
>>  cpu_tuple_cost = .01            # (same) default .01
>>  cpu_index_tuple_cost = .001     # (same) default .001
>>  cpu_operator_cost = 0.0025      # (same) default .0025
>>  # - Genetic Query Optimizer -
>>  geqo = true
>>  geqo_threshold = 20
>>  #geqo_effort = 1
>>  #geqo_generations = 0
>>  #geqo_pool_size = 0             # default based on tables in statement,
>>  #geqo_selection_bias = 2.0      # range 1.5-2.0
>>  # - Other Planner Options -
>>  #default_statistics_target = 10 # range 1-1000
>>  #from_collapse_limit = 8
>>  #join_collapse_limit = 8        # 1 disables collapsing of explicit
>> JOINs
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # ERROR REPORTING AND LOGGING
>>
>> #---------------------------------------------------------------------------
>>
>>  # - Syslog -
>>  #syslog = 0                     # range 0-2; 0=stdout; 1=both; 2=syslog
>>  #syslog_facility = 'LOCAL0'
>>  #syslog_ident = 'postgres'
>>  # - When to Log -
>>  client_min_messages = error
>>  log_min_messages = error
>>  log_error_verbosity = default
>>  log_min_error_statement = panic
>>  log_min_duration_statement = -1
>>
>>  #silent_mode = false             # DO NOT USE without Syslog!
>>
>>  # - What to Log -
>>
>>  debug_print_parse = false
>>  debug_print_rewritten = false
>>  debug_print_plan = false
>>  debug_pretty_print = false
>>  log_connections = false
>>  log_duration = false
>>  log_pid = false
>>  log_statement = false
>>  log_timestamp = false
>>  log_hostname = false
>>  log_source_port = false
>>
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # RUNTIME STATISTICS
>>
>> #---------------------------------------------------------------------------
>>
>>
>>  # - Statistics Monitoring -
>>
>>  log_parser_stats = false
>>  log_planner_stats = false
>>  log_executor_stats = false
>>  log_statement_stats = false
>>
>>  # - Query/Index Statistics Collector -
>>
>>  stats_start_collector = false
>>  stats_command_string = false
>>  stats_block_level = false
>>  stats_row_level = false
>>  stats_reset_on_server_start = false
>>
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # CLIENT CONNECTION DEFAULTS
>>
>> #---------------------------------------------------------------------------
>>
>>
>>  # - Statement Behavior -
>>
>>  #search_path = '$user,public'   # schema names
>>  #check_function_bodies = true
>>  #default_transaction_isolation = 'read committed'
>>  #default_transaction_read_only = false
>>  #statement_timeout = 0          # 0 is disabled, in milliseconds
>>
>>  # - Locale and Formatting -
>>
>>  #datestyle = 'iso, mdy'
>>  #timezone = unknown             # actually, defaults to TZ environment
>> setting
>>  #australian_timezones = false
>>  #extra_float_digits = 0         # min -15, max 2
>>  #client_encoding = sql_ascii    # actually, defaults to database
>> encoding
>>
>>  # These settings are initialized by initdb -- they may be changed
>>  lc_messages = 'en_US.UTF-8'             # locale for system error
>> message
>> strings
>>  lc_monetary = 'en_US.UTF-8'             # locale for monetary formatting
>>  lc_numeric = 'en_US.UTF-8'              # locale for number formatting
>>  lc_time = 'en_US.UTF-8'                 # locale for time formatting
>>
>>  # - Other Defaults -
>>
>>  explain_pretty_print = true
>>  #dynamic_library_path = '$libdir'
>>  #max_expr_depth = 10000         # min 10
>>
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # LOCK MANAGEMENT
>>
>> #---------------------------------------------------------------------------
>>
>>
>>  #deadlock_timeout = 1000        # in milliseconds
>>  #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes
>> each
>>
>>
>>
>> #---------------------------------------------------------------------------
>>
>>  # VERSION/PLATFORM COMPATIBILITY
>>
>> #---------------------------------------------------------------------------
>>
>>
>>  # - Previous Postgres Versions -
>>
>>  #add_missing_from = true
>>  regex_flavor = advanced         # advanced, extended, or basic
>>  #sql_inheritance = true
>>
>>  # - Other Platforms & Clients -
>>
>>  #transform_null_equals = false
>>
>>
>>
>>
>>
>>
>> PLEASE HELP ME FIND THE BOTTLE NECK!!
>>
>> Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds,
>> and that is absolutely unacceptable in terms of online use.  If I
>> can't fix
>> this I'm going to have to spend the month or two switching back to
>> MySQL...
>> and I really don't want to do that, so anything you could do to help us
>> would be amazing!!
>>
>> Thanks in advance!!
>>
>> - Shane
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>                http://archives.postgresql.org
>
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


--
Greg Spiegelberg
  Product Development Manager
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@cranel.com
Technology. Integrity. Focus.