Paolo Tavalazzi <ptavalazzi@charta.it> writes:
> I have reconstructed the database from zero and i have tried the usual two query
> in various conditions :
I dug into this and found two basic problems. One is associated with
handling redundant join conditions, as I said here:
http://archives.postgresql.org/pgsql-hackers/2004-03/msg00592.php
The solution to that is not entirely clear, but hopefully we'll have
something for 7.5 --- and anyway it only seems to cause fractional
estimation errors, not really big mistakes.
The other problem is that you wrote conditions
tran.time >= timestamp '2004-02-20 00:00:00' AND
tran.time <= timestamp '2004-03-08 23:59:59' AND
where tran.time is actually declared as timestamp with time zone.
Had you left off the "timestamp" label for the constants you'd have
been fine, but as it's written you've forced a comparison between
timestamp with timezone vs. timestamp without timezone, That requires
a runtime coercion which is not immutable (because it depends on the
TimeZone parameter). This presently causes the planner to throw up
its hands and make a default estimate ... and in this case the default
estimate is horribly wrong. I got an estimate of 715 rows selected
when the reality was 100000+. That naturally causes all sorts of bad
planning decisions.
I have some thoughts about making this behavior more foolproof for
7.5, as per
http://archives.postgresql.org/pgsql-hackers/2004-03/msg00593.php
but in the meantime you could do a lot better by labeling the constants
timestamptz, or indeed not labeling them at all.
regards, tom lane