Re: how to change the index chosen in plan?
От | Rural Hunter |
---|---|
Тема | Re: how to change the index chosen in plan? |
Дата | |
Msg-id | 4FD2B025.8080308@gmail.com обсуждение исходный текст |
Ответ на | Re: how to change the index chosen in plan? ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Список | pgsql-performance |
于 2012/6/9 0:39, Kevin Grittner 写道: > Rural Hunter <ruralhunter@gmail.com> wrote: > >> How can adjust the statistics target? > > default_statistics_target > > http://www.postgresql.org/docs/current/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > or ALTER TABLE x ALTER COLUMN y SET STATISTICS n > > http://www.postgresql.org/docs/current/interactive/sql-altertable.html Thanks, I will check detail. > >> Sorry the actual tables and query are very complicated so I just >> simplified the problem with my understanding. I rechecked the >> query and found it should be simplified like this: >> select a.* from a inner join b on a.aid=b.aid where a.col1=33 and >> a.col2=44 and a.time<now() and b.bid=8 order by a.time limit 10 >> There is an index on (a.col1,a.col2,a.time). If I remove the >> order-by clause, I can get the plan as I expected. I think that's >> why postgresql selected that index. > > Sounds like it expects the sort to be expensive, which means it > probably expects a large number of rows. An EXPLAIN ANALYZE of the > query with and without the ORDER BY might be instructive. It would > also help to know what version of PostgreSQL you have and how it is > configured, all of which shows up in the results of the query on > this page: > > http://wiki.postgresql.org/wiki/Server_Configuration > Here is the output: name | current_setting -----------------------------+--------------------------------------------------------------------------------------------------------------- version | PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit archive_command | test ! -f /dbbk/postgres/logarch/%f.gz && gzip -c %p >/dbbk/postgres/logarch/%f.gz archive_mode | on autovacuum | on autovacuum_freeze_max_age | 2000000000 checkpoint_segments | 20 client_encoding | UTF8 effective_cache_size | 150GB full_page_writes | off lc_collate | zh_CN.utf8 lc_ctype | zh_CN.utf8 listen_addresses | * log_autovacuum_min_duration | 30min log_destination | stderr log_line_prefix | %t [%u@%h] log_min_duration_statement | 10s log_statement | ddl logging_collector | on maintenance_work_mem | 10GB max_connections | 2500 max_stack_depth | 2MB max_wal_senders | 1 port | 3500 server_encoding | UTF8 shared_buffers | 60GB synchronous_commit | off TimeZone | PRC track_activities | on track_counts | on vacuum_freeze_table_age | 1000000000 wal_buffers | 16MB wal_level | hot_standby work_mem | 8MB (33 rows) >> But still I want the index on b.bid selected first >> for value 8 since there are only several rows with bid 8. though >> for other normal values there might be several kilo to million >> rows. > > An EXPLAIN ANALYZE of one where you think the plan is a good choice > might also help. Ok, I get out a simple version of the actualy query. Here is the explain anaylze without order-by, which is I wanted: http://explain.depesz.com/s/p1p Another with the order-by which I want to avoid: http://explain.depesz.com/s/ujU This is the count of rows in article_label with value 3072(which I referred as table b in previous mail): # select count(*) from article_label where lid=3072; count ------- 56 (1 row) > > Oh, and just to be sure -- are you actually running queries with the > literals like you show, or are you using prepared statements with > placeholders and plugging the values in after the statement is > prepared? Sample code, if possible, might help point to or > eliminate issues with a cached plan. If you're running through a > cached plan, there is no way for it to behave differently based on > the value plugged into the query -- the plan has already been set > before you get to that point. Yes, I ran the query directly wih psql. > > -Kevin >
В списке pgsql-performance по дате отправления: