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 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
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:[EMAIL PROTECTED] 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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
