于 2012/6/9 0:39, Kevin Grittner 写道:
Rural Hunter <ruralhunter@xxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
