Re: PLEASE GOD HELP US!

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(at)cranel(dot)com
Technology. Integrity. Focus.