[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 -----