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

On Wed, Sep 25, 2013 at 10:20 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> 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.

*) date_trunc has same problems as ::date: it is stable expression
only for timestamptz. also, the index will be bigger since you're
still indexing timestamp

*) row wise comparison search might be faster and is generalized to
return N records, not jut one.

merlin