From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | Sam Wong <sam(at)hellosam(dot)net>, postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Slow plan for MAX/MIN or LIMIT 1? |
Date: | 2013-09-25 13:29:26 |
Message-ID: | CAHyXU0x5-oB21Gvf18H+A8jSTq+yBuL-a1V2pjjHC7UM5bEVyw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Tue, Sep 24, 2013 at 4:56 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Tue, Sep 24, 2013 at 6:24 AM, Sam Wong <sam(at)hellosam(dot)net> wrote:
>> This event_log table has 4 million rows.
>>
>> “log_id” is the primary key (bigint),
>>
>> there is a composite index “event_data_search” over (event::text,
>> insert_time::datetime).
>
>
> I think you need to add log_id to that composite index to get pg to use it.
hurk: OP is two statistics misses (one of them massive that are
combing to gobsmack you).
your solution unfortuantely wont work: you can't combine two range
searches in a single index scan. it would probably work if you it
like this. If insert_time is a timestamp, not a timestamptz, we can
convert it to date to get what I think he wants (as long as his
queries are along date boundaries).
how about:
CREATE INDEX ON event_log(event_id, insert_time::date, log_id);
EXPLAIN ANALYZE
SELECT * FROM event_log
WHERE
(event_id, insert_time::date, log_id) >= ('S-Create',
'2013-09-15'::date, 0)
AND event_id = 'S-Create' AND insert_time::date < '2013-09-16'::date
ORDER BY
event_id, insert_time::date, log_id
LIMIT 1
if insert_time is a timestamptz, we can materialize the date into the
table to get around that (timestamptz->date is a stable expression).
If date boundary handling is awkward, our best bet is probably to hack
the planner with a CTE. Note the above query will smoke the CTE based
one.
WITH data AS
(
SELECT log_id FROM event_log
WHERE event='S-Create' AND
insert_time>'2013-09-15' and insert_time<'2013-09-16'
)
SELECT * from data ORDER BY log_id LIMIT 1;
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | AI Rumman | 2013-09-25 15:05:08 | earthdistance query performance |
Previous Message | Sam Wong | 2013-09-25 12:23:04 | Re: Slow plan for MAX/MIN or LIMIT 1? |