Re: Weird case of wrong index choice - Mailing list pgsql-performance

On Tue, Sep 3, 2013 at 8:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Claudio Freire <klaussfreire@gmail.com> writes:
>> So, I've got this query with this very wrong plan:
>
>> explain SELECT min(created) < ((date_trunc('day',now()) - '90
>> days'::interval)) FROM "aggregated_tracks_daily_full" WHERE id BETWEEN
>> 34979048 AND 35179048
>> ;
>
>> QUERY PLAN
>>
-------------------------------------------------------------------------------------------------------------------------------------------------
>>  Result  (cost=795.24..795.26 rows=1 width=0)
>>    InitPlan 1 (returns $0)
>>      ->  Limit  (cost=0.00..795.24 rows=1 width=8)
>>            ->  Index Scan using ix_aggregated_tracks_daily_full_unq on
>> aggregated_tracks_daily_full  (cost=0.00..57875465.87 rows=72777
>> width=8)
>>                  Index Cond: (created IS NOT NULL)
>>                  Filter: ((id >= 34979048) AND (id <= 35179048))
>> (6 rows)
>
>> That plan will scan the entire table, because there is NO row with
>> created null.
>
> No, it won't, because of the LIMIT.  What it will do is scan until it
> finds a row satisfying the "filter" condition.  It's possible that such
> rows only exist towards the high end of the "created" range, but the
> planner is supposing that they're reasonably uniformly distributed.

Well, as usual with serials and timestamps, there's very strong
correlation between created and id, so yes, they're all on the high
end. But they're not 100% correlated either, there's a lot of mixing
there because there's a few dozen processes dumping info there.

Though I'm starting to see the pathological part of this situation:
created has no lower bound on the query, but the correlation and the
fact that I picked the id range to exclude already-archived rows (old,
earlier created dates), means that it'll waste a lot of time skipping
them.

>> I've got no idea why PG is choosing to scan over the
>> unique index,
>
> It's trying to optimize the MIN().  The other plan you show will require
> scanning some thousands of rows, and so is certain to take a lot of time.
> This plan is better except in pathological cases, which unfortunately
> you seem to have one of.

Actually, a lot here is under a second, and I'll be forced to scan all
those rows afterwards anyway to "archive" them, so I'm not worried
about that. It's the figuring out whether it's necessary or not
without scanning already-archived rows.

> If you need this type of query to be reliably fast, you might consider
> creating an index on (created, id), which would allow the correct answer
> to be found with basically a single index probe.

I'll try, though I was trying to make do with the indices I already
have rather than add more, the PK should do (because it's a huge table
with tons of updates, so I gotta keep it lean).

I don't see how such an index would help either... since the ids are
second level and can't be range-queried... unless it's because then
analyze will see the correlation?

Perhaps I should just add an "archived" bit and a partial index on
created where not archived. I was just puzzled by the planner's bad
choice, but I see it's the correlation hurting here, and that's a hard
problem lots of hackers are attacking anyway.