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 > 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 > 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. 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. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
