Re: Slow plan for MAX/MIN or LIMIT 1?

On Wed, Sep 25, 2013 at 10:29 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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).

I was thinking an index over:

(event, date_trunc('day', insert_time), log_id)

And the query like

SELECT min(log_id) FROM event_log
WHERE event='S-Create' AND
date_trunc('day',insert_time) = '2013-09-15'

That's a regular simple range scan over the index.