Re: Query plan question, and a memory leak - Mailing list pgsql-general

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > I wouldn't expect it to actually take any more time. In fact I would expect it
> > to take a lot less time since it takes time to handle the resulting data too.
>
> You're mistaking planner estimate time for reality ;-).
>
> IIRC, the planner doesn't bother to account for evaluation time of
> select-list values in its estimates.  At least in simple cases, there's
> no point in doing that math because the cost will be the same no matter
> what plan is chosen.

Yeah after further thought I realized it makes sense for the optimizer not to
bother taking into account the result set since in theory the result set
should be the same regardless of the plan.

However I tested those queries with some data and things really do seem to be
behaving oddly. It takes nearly twice as long to run the version with the
where clause and duplicate subplan. And the analyze output seems to indicate
that it is in fact being executed.

Even then, the cost is way more than twice the cost without the where clause:

slo=> explain analyze select * from (select id, (select id from words where id=w.id) as x from words as w) as z ;
                                                        QUERY PLAN
   

---------------------------------------------------------------------------------------------------------------------------
 Subquery Scan z  (cost=0.00..983.92 rows=45392 width=4) (actual time=14.02..1988.66 rows=45392 loops=1)
   ->  Seq Scan on words w  (cost=0.00..983.92 rows=45392 width=4) (actual time=14.01..1796.42 rows=45392 loops=1)
         SubPlan
           ->  Index Scan using idx on words  (cost=0.00..3.02 rows=1 width=4) (actual time=0.02..0.03 rows=1
loops=45392)
                 Index Cond: (id = $0)
 Total runtime: 2049.16 msec
(6 rows)

Time: 2050.95 ms
slo=> explain analyze select * from (select id, (select id from words where id=w.id) as x from words as w) as z where x
isnot null; 
                                                        QUERY PLAN
   

---------------------------------------------------------------------------------------------------------------------------
 Subquery Scan z  (cost=0.00..138006.65 rows=45165 width=4) (actual time=2.19..3599.57 rows=45392 loops=1)
   ->  Seq Scan on words w  (cost=0.00..138006.65 rows=45165 width=4) (actual time=2.18..3417.73 rows=45392 loops=1)
         Filter: ((subplan) IS NOT NULL)
         SubPlan
           ->  Index Scan using idx on words  (cost=0.00..3.02 rows=1 width=4) (actual time=0.02..0.03 rows=1
loops=45392)
                 Index Cond: (id = $0)
           ->  Index Scan using idx on words  (cost=0.00..3.02 rows=1 width=4) (actual time=0.02..0.03 rows=1
loops=45392)
                 Index Cond: (id = $0)
 Total runtime: 3662.43 msec
(9 rows)

Time: 3664.63 ms



--
greg