Message-ID: <4FD2B025.8080308@gmail.com>
Date: Sat, 09 Jun 2012 10:08:37 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
	rv:13.0) Gecko/20120604 Thunderbird/13.0
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: how to change the index chosen in plan?
References: <4FD1FF14.9060303@gmail.com> <11881.1339164658@sss.pgh.pa.us>
	<4FD20AD0.3040902@gmail.com>
	<4FD1C7C702000025000481E7@gw.wicourts.gov>
	<4FD226E6.2060705@gmail.com>
	<4FD1E47A02000025000481FC@gw.wicourts.gov>
In-Reply-To: <4FD1E47A02000025000481FC@gw.wicourts.gov>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

于 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
>