Thread: Weird case of wrong index choice

This is postgres 9.1.9.

I'm getting a very weird case in which a simple range query over a PK
picks the wrong... the very very wrong index.

The interesting thing, is that I've got no idea why PG is so grossly
mis-estimating the number of rows scanned by the wrong plan.

I've got this table that's a bunch of counters grouped by a bunch of
other columns, and a date.

The PK is a simple serial type, and the unique index you'll see is
over (created, expr1, expr2, ... expr2) where created is the date, and
exprN are expressions involving the grouping columns.

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. I've got no idea why PG is choosing to scan over the
unique index, given that 1) there's 0 rows outside the index
condition, so it'll scan the entire table, and 2) i've analyzed and
vacuum analyzed repeatedly, no chage, and 3) there's the PK index that
works flawless.

The table is very big. So scanning it entriely in random fashion isn't smart.

I can force the right plan like this:

mat=# explain SELECT min(created) < ((date_trunc('day',now()) - '90
days'::interval)) FROM (select id,created FROM
"aggregated_tracks_daily_full" WHERE id BETWEEN 34979048 AND 35179048
ORDER BY id) t;
                                                             QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=89416.49..89416.51 rows=1 width=8)
   ->  Index Scan using aggregated_tracks_daily_full_pkey on
aggregated_tracks_daily_full  (cost=0.00..88506.78 rows=72777
width=16)
         Index Cond: ((id >= 34979048) AND (id <= 35179048))
(3 rows)


But i'm wondering why I have to. There's something hinky there.

PS: The point of the query is to know whether there's something to be
"archived" (ie, too old) in that id range.

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.

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

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.

            regards, tom lane

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.