Skip site navigation (1) Skip section navigation (2)

Re: optimizing constant quals within outer joins

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Phil Frost <indigo(at)bitglue(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: optimizing constant quals within outer joins
Date: 2006-06-29 00:43:23
Message-ID: 87d5csvljo.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Phil Frost <indigo(at)bitglue(dot)com> writes:
> > The planner in fact can move the function around without changing the
> > output.
> 
> Not when it's within the nullable side of an outer join --- moving a
> WHERE clause up out of that would make the difference between no row
> out, and a null-extended row out, which are certainly not the same.
> 
> I'm not sure why it's not pulling up from the left side of the left join
> though.  That might be a bug.  What PG version is this exactly?

In fact it doesn't even pull it up out of a regular join. I looked into this
when it was first brought up on IRC and as near as I can tell it is trying to
do so and somehow just failing.


postgres=# create function foo(text) returns bool as 'select case when $1 = ''foo'' then true else false end' language sql stable strict ;


postgres=# explain select 1 from a,a as b where foo('foo') ;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Result  (cost=31.34..75332.74 rows=3763600 width=0)
   One-Time Filter: foo('foo'::text)
   ->  Nested Loop  (cost=31.34..75332.74 rows=3763600 width=0)
         ->  Seq Scan on a  (cost=0.00..29.40 rows=1940 width=0)
         ->  Materialize  (cost=31.34..50.74 rows=1940 width=0)
               ->  Seq Scan on a b  (cost=0.00..29.40 rows=1940 width=0)
(6 rows)


postgres=# explain select 1 from (select * from a where foo('foo')) as x, a;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Nested Loop  (cost=31.34..25169.19 rows=1255180 width=0)
   ->  Seq Scan on a  (cost=0.00..34.25 rows=647 width=0)
         Filter: foo('foo'::text)
   ->  Materialize  (cost=31.34..50.74 rows=1940 width=0)
         ->  Seq Scan on a  (cost=0.00..29.40 rows=1940 width=0)
(5 rows)


-- 
greg


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-06-29 03:23:17
Subject: Re: [GENERAL] UUID's as primary keys
Previous:From: Greg StarkDate: 2006-06-29 00:34:11
Subject: Re: [GENERAL] UUID's as primary keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group