From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marc G. Fournier <scrappy@postgresql.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: timestamp query doesn't use an index ...
Date: Sun, 21 May 2006 13:18:36 -0400
Message-ID: <7007.1148231916@sss.pgh.pa.us> (raw)
In-Reply-To: <20060521140506.N1114@ganymede.hub.org>
References: <20060520223704.T1114@ganymede.hub.org>
<5287.1148225462@sss.pgh.pa.us>
<20060521140506.N1114@ganymede.hub.org>
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> -> Bitmap Heap Scan on page_schedule ps2 (cost=573.65..2822.86 rows=91614 width=16) (actual time=149.788..505.438 rows=94798 loops=1)
> Recheck Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17 08:09:18'::timestamp without time zone)
> -> Bitmap Index Scan on start_time_page_schedule (cost=0.00..573.65 rows=91614 width=0) (actual time=127.761..127.761 rows=94798 loops=1)
> Index Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17 08:09:18'::timestamp without time zone)
> And yup, it is definitely returning just 128 rows out of the 93k or so:
No, the scan is pulling 94798 rows from the table, according to the
EXPLAIN ANALYZE --- the number of resulting groups isn't much of a
factor here.
We don't currently have any index optimization for MIN/MAX in a GROUP BY
context, and even if we did, it wouldn't apply here: the planner
couldn't assume that the sort order of an index on "start_time at time
zone 'MST7MDT'" would have anything to do with the ordering of just
"start_time". Is there a reason you're writing
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
and not
where ps2.start_time <= '2006-5-17 8:9:18' at time zone 'MST7MDT'
The latter seems less likely to have strange behaviors near DST
transitions. I don't think it'll be any faster at the moment, but you
could at least save maintaining a specialized index.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, scrappy@postgresql.org
Subject: Re: timestamp query doesn't use an index ...
In-Reply-To: <7007.1148231916@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