From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claudio Freire <klaussfreire@gmail.com>
cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Weird case of wrong index choice
In-reply-to: 
 <CAGTBQpYq7KbYTb1PTxshLZJT5Ntpnfy4nNYBP1HDAGo2=G2Z3A@mail.gmail.com>
References: 
 <CAGTBQpYq7KbYTb1PTxshLZJT5Ntpnfy4nNYBP1HDAGo2=G2Z3A@mail.gmail.com>
Comments: In-reply-to Claudio Freire <klaussfreire@gmail.com>
 message dated "Tue, 03 Sep 2013 18:47:11 -0300"
Date: Tue, 03 Sep 2013 19:11:42 -0400
Message-ID: <7951.1378249902@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance