[reedstrm@ece.rice.edu: Re: [SQL] Query problems with 7.0 beta 5]

Sorry for the forward, for those who read both, but I've forgotten whether
Tom reads [SQL], but I think this one's an actual planner/optimizer
problem/difference between 6.5.3 and 7.0. I've snipped Jason's
demonstration that on 6.5.3 he got an index scan, using the subselect.

Ross

----- Forwarded message from "Ross J. Reedstrom" <reedstrm@ece.rice.edu> -----
On Wed, May 03, 2000 at 11:54:37AM -0700, Jason Earl wrote:

I am currently working on migrating an application
from PostgreSQL 6.5.3 to 7.0 and I just noticed that
one of my favorite queries no longer works as I would
hope.

Any actual timings? I presume the index scan is slower,
since the subselect is just returning a constant, but
you don't mention if it's significantly slower.

explain select * from caseweights1 where dt > (select
'now'::datetime - '15 mins'::interval);
NOTICE: QUERY PLAN:

Seq Scan on caseweights1 (cost=0.00..136204.66
rows=2228391 width=28)
InitPlan
-> Result (cost=0.00..0.00 rows=0 width=0)

EXPLAIN

Hmm, looks to me like the planner is estimating that something like 2
million of the 7 million rows are going to be returned. It'd be reasonable
to do the sequential scan, then, since it'd probably be faster than
going to the index, as well.

As you can guess this query takes a _long_ time. I
have tried replacing 'now'::datetime with
'now'::timestamp (that's what the dt column is now)
and I have also used the now() function. Both of
these queries give me similar query plans.

However, if I supply a timestamp it uses the index
like I would expect it to:

explain select * from caseweights1 where dt > 'Wed May
03 12:12:11 2000 MDT';
NOTICE: QUERY PLAN:

Index Scan using caseweights1_dt_idx on caseweights1
(cost=0.00..25041.89 rows=6685 width=28)

EXPLAIN

Now, it looks like the planner is expecting only ~7K rows, so it goes
with the index. I can't get this to replicate here, since I've only got
140 values in my test table.

I presume you've run VACUUM ANALYZE recently? If so, it's a matter of
the planner realizing that the RESULT from the subselect is a timestamp
constant, and so should use the same estimator as a literal constant. The
man for this job is Tom Lane. Any ideas, Tom?

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

----- End forwarded message -----