pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
IN() Optimization issue in 8.0rc5
3+ messages / 2 participants
[nested] [flat]

* IN() Optimization issue in 8.0rc5
@ 2005-01-15 20:23 Josh Berkus <josh@agliodbs.com>
  2005-01-15 20:53 ` Re: IN() Optimization issue in 8.0rc5 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2005-01-15 20:23 UTC (permalink / raw)
  To: pgsql-performance

Tom,

Hmmm ... I'm seeing an issue with IN() optimization -- or rather the lack of 
it -- in 8.0rc5.    It seems to me that this worked better in 7.4, although 
I've not been able to load this particular database and test

dm=# explain
dm-# SELECT personid FROM mr.person_attributes_old
dm-#                                        WHERE personid NOT IN (SELECT 
personid FROM mr.person_attributes);
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Seq Scan on person_attributes_old  (cost=0.00..3226144059.85 rows=235732 
width=4)
   Filter: (NOT (subplan))
   SubPlan
     ->  Seq Scan on person_attributes  (cost=0.00..12671.07 rows=405807 
width=4)
(4 rows)

dm=# explain select pao.personid from mr.person_attributes_old pao
dm-# left outer join mr.person_attributes p on pao.personid = p.personid
dm-# where p.personid is null;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=0.00..34281.83 rows=471464 width=4)
   Merge Cond: ("outer".personid = "inner".personid)
   Filter: ("inner".personid IS NULL)
   ->  Index Scan using idx_opa_person on person_attributes_old pao  
(cost=0.00..13789.29 rows=471464 width=4)
   ->  Index Scan using idx_pa_person on person_attributes p  
(cost=0.00..14968.25 rows=405807 width=4)
(5 rows)

It seems like the planner ought to recognize that the first form of the query 
is optimizable into the 2nd form, and that I've seen it do so in 7.4.   
However, *no* amount of manipulation of query parameters I did on the 1st 
form of the query were successful in getting the planner to recognize that it 
could use indexes for the IN() form of the query.

Thoughts?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: IN() Optimization issue in 8.0rc5
  2005-01-15 20:23 IN() Optimization issue in 8.0rc5 Josh Berkus <josh@agliodbs.com>
@ 2005-01-15 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-01-15 21:32   ` Re: IN() Optimization issue in 8.0rc5 Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2005-01-15 20:53 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> dm=# explain
> dm-# SELECT personid FROM mr.person_attributes_old
> dm-#                                        WHERE personid NOT IN (SELECT 
> personid FROM mr.person_attributes);
>                                     QUERY PLAN
> -----------------------------------------------------------------------------------
>  Seq Scan on person_attributes_old  (cost=0.00..3226144059.85 rows=235732 
> width=4)
>    Filter: (NOT (subplan))
>    SubPlan
>      ->  Seq Scan on person_attributes  (cost=0.00..12671.07 rows=405807 
> width=4)
> (4 rows)

Hmm.  What you want for a NOT IN is for it to say
   Filter: (NOT (hashed subplan))
which you are not getting.  What's the datatypes of the two personid
columns?  Is the 400k-row estimate for person_attributes reasonable?
Maybe you need to increase work_mem (nee sort_mem) to allow a
400k-row hash table?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: IN() Optimization issue in 8.0rc5
  2005-01-15 20:23 IN() Optimization issue in 8.0rc5 Josh Berkus <josh@agliodbs.com>
  2005-01-15 20:53 ` Re: IN() Optimization issue in 8.0rc5 Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-15 21:32   ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2005-01-15 21:32 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>

Tom,

> Hmm.  What you want for a NOT IN is for it to say
>    Filter: (NOT (hashed subplan))
> which you are not getting.  What's the datatypes of the two personid
> columns? 

INT

> Is the 400k-row estimate for person_attributes reasonable? 

Yes, the estimates are completely accurate.

> Maybe you need to increase work_mem (nee sort_mem) to allow a
> 400k-row hash table?

Aha, that's it.  I thought I'd already set that, but apparently it was a 
different session.    Fixed.  Thanks!

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-01-15 21:32 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-15 20:23 IN() Optimization issue in 8.0rc5 Josh Berkus <josh@agliodbs.com>
2005-01-15 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-15 21:32   ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox