Hi Kevin,
Thanks for your detailed explanation.
于 2012/6/8 22:37, Kevin Grittner 写道:
Rural Hunter <ruralhunter@xxxxxxxxx> wrote:
于2012年6月8日 22:10:58,Tom Lane写到:
Rural Hunter <ruralhunter@xxxxxxxxx> writes:
I have a query like this:
select a.* from a inner join b on a.aid=b.aid where a.col1=33
a.col2=44 and b.bid=8
postgresql selected the index on a.col1 then selected the index
on b.bid. But in my situation, I know that the query will be
faster if it chose the index on b.bid first since there are only
a few rows with value 8.
If you know that and the planner doesn't, maybe ANALYZE is called
for.
No, it's not the analyze problem.
So you ran ANALYZE and retried? If not, please do.
Yes, I did.
For some other values on b.bid such as 9, 10, the plan is fine
since there a a lot of rows in table b for them.
So it uses the same plan regardless of the number of rows in table b
for the value?
yes.
That sure *sounds* like you need to run ANALYZE,
possibly after adjusting the statistics target for a column or two.
How can adjust the statistics target?
But for some specific values such as 8 I want the plan changed.
If you approach it from that line of thought, you will be unlikely
to reach a good long-term solution. PostgreSQL has a costing model
to determine which plan is expected to be cheapest (fastest). This
is based on statistics gathered during ANALYZE and on costing
factors. Generally, if it's not choosing the fastest plan, you
aren't running ANALYZE frequently enough or with a fine-grained
enough statistics target _or_ you need to adjust your costing
factors to better model your actual costs.
You haven't given us a lot of clues about which it is that you need
to do, but there is *some* suggestion that you need to ANALYZE. If
you *try* that and it doesn't solve your problem, please read this
page and provide more information:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
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. 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.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
