Christophe Escobar <christophe.esco@gmail.com> writes:
> I have some performance issues when trying to fetch rows from the table
> with a specific query, I suspect the planner to choose the wrong index
> because of the limit.
> ...
> 1) Why is the planner changing index scanning at the threshold of 45 for
> the LIMIT ?
That's just where the crossover point happens to fall for the estimated
costs of the two plans. Of course, the costs are based on the estimate
of 2061 rows matching the WHERE conditions, and since the true figure
is 0 rows, the cost estimates are bad :-(
> 2) Is it possible for a specific query to force the planner on choosing a
> given index or preventing it from choosing one ?
In this example I'd be inclined to prevent the ORDER BY from being
considered while choosing an index, which you can do with the traditional
optimization fence of OFFSET 0:
SELECT * FROM
(SELECT * FROM notifications
WHERE bucket_id IN (30231,30230,30104) AND type IN ('foo', 'bar')
OFFSET 0) ss
ORDER BY created_at DESC limit 20;
Of course, if you have cases where the WHERE conditions will select
a large number of rows, this will prevent the planner from making
a wise choice in those cases --- the use of the created_at index isn't
inherently stupid, it all depends on how many rows match the WHERE.
Depending on how wedded you are to your current data representation,
it might be better to redesign the table so that you don't need to
test two independent columns to select the rows you care about.
That would improve the odds of getting a decent rowcount estimate.
regards, tom lane