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

>         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