Re: how to change the index chosen in plan?

于 2012/6/9 0:39, Kevin Grittner 写道:
> Rural Hunter <ruralhunter(at)gmail(dot)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(at)%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
>