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
