From: Tobias Brox <tobias@nordicbet.com>
To: Richard Huxton <dev@archonet.com>
Cc: Tobias Brox <tobias@nordicbet.com>
Cc: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner statistics, correlations
Date: Fri, 12 Jan 2007 10:56:55 +0100
Message-ID: <20070112095655.GA5128@oppetid.no> (raw)
In-Reply-To: <45A7523C.3000007@archonet.com>
References: <20070112081631.GA10636@oppetid.no>
<a2de01dd0701120056p37f1906excd71068468e5769a@mail.gmail.com>
<20070112090500.GB16243@oppetid.no>
<45A7523C.3000007@archonet.com>
[Richard Huxton - Fri at 09:17:48AM +0000]
> Try a partial index:
> CREATE INDEX my_new_index ON events (event_time)
> WHERE state in (1,2,3);
I have that, the index is used and the query is lightning fast - the
only problem is that the planner is using the wrong estimates. This
becomes a real problem when doing joins and more complex queries.
> Now, if that doesn't work you might want to split the query into two...
Hm, that's an idea - to use a two-pass query ... first:
select max(event_time) from events where state in (1,2,3);
and then use the result:
select * from events
where event_time>? and event_time<now() and state in (1,2,3)
This would allow the planner to get the estimates in the right ballpark
(given that the events won't stay for too long in the lower states), and
it would in any case not be significantly slower than the straight-ahead
approach - but quite inelegant.
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: tobias@nordicbet.com, dev@archonet.com, peterachilds@gmail.com
Subject: Re: Planner statistics, correlations
In-Reply-To: <20070112095655.GA5128@oppetid.no>
* 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