Re: PLEASE GOD HELP US!
| От | Greg Spiegelberg |
|---|---|
| Тема | Re: PLEASE GOD HELP US! |
| Дата | |
| Msg-id | 415D884A.7090507@cranel.com обсуждение исходный текст |
| Ответ на | Re: PLEASE GOD HELP US! ("Joshua D. Drake" <jd@commandprompt.com>) |
| Список | pgsql-admin |
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.
В списке pgsql-admin по дате отправления: