From: James Cloos <cloos@jhcloos.com>
To: pgsql-sql@postgresql.org
Subject: A more efficient way?
User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/24.0.50 (gnu/linux)
Face: iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABHNCSVQICAgIfAhkiAAAAI1J
	REFUOE+lU9ESgCAIg64P1y+ngUdxhl5H8wFbbM0OmUiEhKkCYaZThXCo6KE5sCbA1DDX3genvO4d
	eBQgEMaM5qy6uWk4SfBYfdu9jvBN9nSVDOKRtwb+I3epboOsOX5pZbJNsBJFvmQQ05YMfieIBnYX
	FK2N6dOawd97r/e8RjkTLzmMsiVgrAoEugtviCM3v2WzjgAAAABJRU5ErkJggg==
Copyright: Copyright 2009 James Cloos
OpenPGP: ED7DAEA6; url=http://jhcloos.com/public_key/0xED7DAEA6.asc
OpenPGP-Fingerprint: E9E9 F828 61A4 6EA9 0F2B  63E7 997A 9F17 ED7D AEA6
Date: Sun, 31 Oct 2010 02:59:20 -0400
Message-ID: <m3sjzm4zkv.fsf@jhcloos.com>
Lines: 42
MIME-Version: 1.0
Content-Type: text/plain

I've a third-party app which is slower than it ought to be.

It does one operation in particular by doing a query to get a list of
rows, and then iterates though them to confirm whether it actually wants
that row.  As you can imagine that is very slow.

This query gets the correct data set in one go, but is also slow:

 select p from m where s = 7 and p not in (select p from m where s != 7);

The p column is not unique; the goal is the set of p for which *every*
row with a matching p also has s=7.  (s and p are both integer columns,
if that matters.)

That takes about 38 seconds with this (explain analyze) plan:

                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using m_5 on m  (cost=8141.99..11519.73 rows=1 width=4) (actual time=564.689..37964.163 rows=243 loops=1)
   Index Cond: (s = 7)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=8141.99..13586.24 rows=523955 width=4) (actual time=0.003..80.734 rows=523948 loops=243)
           ->  Seq Scan on m  (cost=0.00..7413.34 rows=523955 width=4) (actual time=0.023..259.901 rows=523948 loops=1)
                 Filter: (s <> 7)
 Total runtime: 38121.781 ms

The index m_5 is btree (s).  

Can that be done is a way which requires only 1 loop?

My understanding is that:

 select p from m where s = 7  except select p from m where s != 7;

will return the same results as a simple 'select p from m where s = 7', yes?

Is there a way to do it with a self join which requires just a single loop?

-JimC
-- 
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6