[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(at)ece(dot)rice(dot)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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

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