Re: Query Plan choice with timestamps

On 07/ago/08, at 14:36, Richard Huxton wrote:

> Giorgio Valoti wrote:
>> On 07/ago/08, at 10:35, Richard Huxton wrote:
>>> Giorgio Valoti wrote:
>>>> Hi, I have a timestamptz field that I want to use with a query,
>>>> but I don’t need the full timestamp resolution, so I’ve created a
>>>> day_trunc(timestamptz) immutable function which I’ll use with the
>>>> query and with a new index:
>>>> logs=> create index test_idx on blackbox (day_trunc(ts));
>>>> However, the query plan doesn’t use the index:
>>>
>>> Does it use it ever? e.g. with
>>> SELECT * FROM blackbox WHERE day_trunk(ts) = '...'
>> It’s used:
> [snip]
>
> OK - so the index is working.
>
> If you disable seq-scans before running the query, does it use it
> then?
>
> SET enable_seqscan = off;

Yes

> […]
>
> In particular:
> 1. Is the estimated cost more or less than 119773.92?

                                        QUERY PLAN
-----------------------------------------------------------------------------------------
  GroupAggregate  (cost=0.00..122309.32 rows=74226 width=8)
    ->  Index Scan using date_idx on blackbox  (cost=0.00..101586.31
rows=247736 width=8)

>
> 2. How does that match the actual time taken?

                                                                 QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
  GroupAggregate  (cost=0.00..122309.32 rows=74226 width=8) (actual
time=0.222..1931.651 rows=428 loops=1)
    ->  Index Scan using date_idx on blackbox  (cost=0.00..101586.31
rows=247736 width=8) (actual time=0.072..1861.367 rows=247736 loops=1)
  Total runtime: 1931.782 ms

But I haven’t revised the vacuum settings.

Thank you
--
Giorgio Valoti