From: | Ron Mayer <ron(at)intervideo(dot)com> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Cc: | Ron Mayer <ron(at)intervideo(dot)com> |
Subject: | Should the optimizer optimize "current_date - interval '1 days'" (fwd) |
Date: | 2002-06-03 19:37:48 |
Message-ID: | Pine.LNX.4.33.0206031236310.12984-100000@ron |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On a very big table (a data warehouse with >10 million rows), I frequently
run queries looking at the past few days.
However queries like this:
select count(*)
from fact
where dat > (current_date - interval '1 days');
never uses the index I have on "fact". (Thanks to previous queries it's now
ordered by 'dat' so the correlation in pg_stats is '1'.).
However if I toss on an extra where clause with a constant like
select count(*)
from fact
where dat > (current_date - interval '1 days')
and dat > '2002-05-20';
it hapily uses the index (at least for the second comparison).
Should it treat my current_dat... expression as a constant and use
the index? Or is there a good reason it doesn't?
Thanks,
Ron
PS: This seems true even if I "set enable_seqscan to off".
logs2=# set enable_seqscan to off;
logs2=# explain
logs2-# select count(*) from fact
logs2-# where dat > (current_date - interval '1 days');
NOTICE: QUERY PLAN:
Aggregate (cost=101265332.77..101265332.77 rows=1 width=0)
-> Seq Scan on fact (cost=100000000.00..101231544.46 rows=13515325 width=0)
logs2=# explain
logs2-# select count(*)
logs2-# from fact
logs2-# where dat > (current_date - interval '1 days')
logs2-# and dat > '2002-05-20';
NOTICE: QUERY PLAN:
Aggregate (cost=198729.54..198729.54 rows=1 width=0)
-> Index Scan using i__fact__dat on fact (cost=0.00..194279.24
rows=1780119 width=0)
EXPLAIN
logs2=#
From | Date | Subject | |
---|---|---|---|
Next Message | Nico Erichsen | 2002-06-03 19:55:30 | Transaction isolation with concurrent increments |
Previous Message | Rob Butler | 2002-06-03 13:19:45 | Re: PQescapeString and PQescapeBytea not exported during win 32 build |