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

>         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


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachments:

jd.vcf (text/x-vcard, 638B) download begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard

view thread (33+ messages)  latest in thread

Message-ID: <415D8487.5080900@commandprompt.com>
Permalink:  ../415D8487.5080900@commandprompt.com/
Also on:    postgresql.org/message-id/415D8487.5080900@commandprompt.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: jd@commandprompt.com, shanew@skinnycorp.com
  Subject: Re: PLEASE GOD HELP US!
  In-Reply-To: <415D8487.5080900@commandprompt.com>
  References: <002801c4a7d0$66797ad0$3402a8c0@shanepc> <415D8487.5080900@commandprompt.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