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