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
Date: Tue, 03 Sep 2013 19:11:42 -0400
Message-ID: <7951.1378249902@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGTBQpYq7KbYTb1PTxshLZJT5Ntpnfy4nNYBP1HDAGo2=G2Z3A@mail.gmail.com>
References: <CAGTBQpYq7KbYTb1PTxshLZJT5Ntpnfy4nNYBP1HDAGo2=G2Z3A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, klaussfreire@gmail.com
Subject: Re: Weird case of wrong index choice
In-Reply-To: <7951.1378249902@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox