pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Spiegelberg <gspiegelberg@cranel.com>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: Shane|SkinnyCorp <shanew@skinnycorp.com>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: PLEASE GOD HELP US!
Date: Fri, 01 Oct 2004 12:39:38 -0400
Message-ID: <415D884A.7090507@cranel.com> (raw)
In-Reply-To: <415D8487.5080900@commandprompt.com>
References: <002801c4a7d0$66797ad0$3402a8c0@shanepc>
	<415D8487.5080900@commandprompt.com>

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.




view thread (33+ messages)  latest in thread

Message-ID: <415D884A.7090507@cranel.com>
Permalink:  ../415D884A.7090507@cranel.com/
Also on:    postgresql.org/message-id/415D884A.7090507@cranel.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: gspiegelberg@cranel.com, jd@commandprompt.com, shanew@skinnycorp.com
  Subject: Re: PLEASE GOD HELP US!
  In-Reply-To: <415D884A.7090507@cranel.com>
  References: <002801c4a7d0$66797ad0$3402a8c0@shanepc> <415D8487.5080900@commandprompt.com> <415D884A.7090507@cranel.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox