From: "Peter Darley" <pdarley@kinesis-cem.com>
To: <pgsql-performance@postgresql.org>
Subject: Query Plan far worse in 7.3.2 than 7.2.1 
Date: Wed, 30 Apr 2003 07:39:24 -0700
Message-ID: <NNEAICKPNOGDBHNCEDCPIEHGDMAA.pdarley@kinesis-cem.com>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_000F_01C30EEB.9F203F60"
Importance: Normal
In-Reply-To: <18324.1051657553@sss.pgh.pa.us>

This is a multi-part message in MIME format.

------=_NextPart_000_000F_01C30EEB.9F203F60
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Friends,
	I've got a query that has stopped using an index scan between 7.2.1 or RH
7.1 and 7.3.2 or RH 8.0, and I can't figure out why.  I've come up with a
replacement query which is a whole lot faster, but again, I can't tell why.

The original query (condensed to remove the uninteresting bits) is:

SELECT COUNT(*) FROM Border_Shop_List  WHERE NOT EXISTS (SELECT Foreign_Key
FROM Sample WHERE Foreign_Key='Quantum_' || Border_Shop_List.Assignment_ID
|| '_' || Assignment_Year || '_' || Evaluation_ID)

This runs in 667055.79 msec

The new one is:

SELECT COUNT(*) FROM Border_Shop_List  WHERE 'Quantum_' ||
Border_Shop_List.Assignment_ID || '_' || Border_Shop_List.Assignment_Year ||
'_' || Border_Shop_List.Evaluation_ID NOT IN  (SELECT Foreign_Key FROM
Sample WHERE Foreign_Key IS NOT NULL)

This runs in 16500.83 msec (~1/40th the time)

	Again, my immediate problem is solved, but I'm trying to understand why
there is such a speed difference.

	I've attached explains for the two querys in both versions.

	The schemas for the two databases are identical.  If there's more info
people need, just let me know.

Thanks,
Peter Darley

------=_NextPart_000_000F_01C30EEB.9F203F60
Content-Type: text/plain;
	name="NewQuery.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="NewQuery.txt"

SELECT COUNT(*) FROM Border_Shop_List  WHERE 'Quantum_' || Border_Shop_List=
.Assignment_ID || '_' || Border_Shop_List.Assignment_Year || '_' || Border_=
Shop_List.Evaluation_ID NOT IN  (SELECT Foreign_Key FROM Sample WHERE Forei=
gn_Key IS NOT NULL)


7.3.2
neo=3D# explain analyze SELECT COUNT(*) FROM Border_Shop_List  WHERE 'Quant=
um_' || Border_Shop_List.Assignment_ID || '_' || Border_Shop_List.Assignmen=
t_Year || '_' || Border_Shop_List.Evaluation_ID NOT IN  (SELECT Foreign_Key=
 FROM Sample WHERE Foreign_Key IS NOT NULL);
                                                          QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------
 Aggregate  (cost=3D2460448.75..2460448.75 rows=3D1 width=3D0) (actual time=
=3D16500.14..16500.14 rows=3D1 loops=3D1)
   ->  Seq Scan on border_shop_list  (cost=3D0.00..2460447.50 rows=3D500 wi=
dth=3D0) (actual time=3D3245.59..16500.11 rows=3D3 loops=3D1)
         Filter: (subplan)
         SubPlan
           ->  Materialize  (cost=3D2460.41..2460.41 rows=3D2741 width=3D34=
) (actual time=3D0.02..0.45 rows=3D1533 loops=3D3065)
                 ->  Seq Scan on sample  (cost=3D0.00..2460.41 rows=3D2741 =
width=3D34) (actual time=3D41.89..50.75 rows=3D3062 loops=3D1)
                       Filter: (foreign_key IS NOT NULL)
 Total runtime: 16500.83 msec
(8 rows)


7.2.1
neo=3D# explain SELECT COUNT(*) FROM Border_Shop_List  WHERE 'Quantum_' || =
Border_Shop_List.Assignment_ID || '
_' || Border_Shop_List.Assignment_Year || '_' || Border_Shop_List.Evaluatio=
n_ID NOT IN  (SELECT Foreign_Key
FROM Sample WHERE Foreign_Key IS NOT NULL);
NOTICE:  QUERY PLAN:

Aggregate  (cost=3D11323743.75..11323743.75 rows=3D1 width=3D0)
  ->  Seq Scan on border_shop_list  (cost=3D0.00..11323742.50 rows=3D500 wi=
dth=3D0)
        SubPlan
          ->  Seq Scan on sample  (cost=3D0.00..22647.41 rows=3D33457 width=
=3D34)

EXPLAIN
------=_NextPart_000_000F_01C30EEB.9F203F60
Content-Type: text/plain;
	name="OldQuery.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="OldQuery.txt"

SELECT COUNT(*) FROM Border_Shop_List  WHERE NOT EXISTS (SELECT Foreign_Key=
 FROM Sample WHERE Foreign_Key=3D'Quantum_' || Border_Shop_List.Assignment_=
ID || '_' || Assignment_Year || '_' || Evaluation_ID)


7.3.2
neo=3D# explain analyze SELECT COUNT(*) FROM Border_Shop_List  WHERE NOT EX=
ISTS (SELECT Foreign_Key FROM Sample WHERE Foreign_Key=3D'Quantum_' || Bord=
er_Shop_List.Assignment_ID || '_' || Assignment_Year || '_' || Evaluation_I=
D);
                                                                   QUERY PL=
AN
---------------------------------------------------------------------------=
---------------------------------------------------------------------
 Aggregate  (cost=3D3959148.75..3959148.75 rows=3D1 width=3D0) (actual time=
=3D667055.64..667055.64 rows=3D1 loops=3D1)
   ->  Seq Scan on border_shop_list  (cost=3D0.00..3959147.50 rows=3D500 wi=
dth=3D0) (actual time=3D110283.60..667055.60 rows=3D3 loops=3D1)
         Filter: (NOT (subplan))
         SubPlan
           ->  Seq Scan on sample  (cost=3D0.00..3959.13 rows=3D1 width=3D3=
4) (actual time=3D217.62..217.62 rows=3D1 loops=3D3065)
                 Filter: ((foreign_key)::text =3D ((((('Quantum_'::text || =
($0)::text) || '_'::text) || ($1)::text) || '_'::text) || ($2)::text))
 Total runtime: 667055.79 msec
(7 rows)

7.2.1
neo=3D# explain SELECT COUNT(*) FROM Border_Shop_List  WHERE NOT EXISTS (SE=
LECT Foreign_Key FROM Sample WHERE Foreign_Key=3D'Quantum_' || Border_Shop_=
List.Assignment_ID || '_' || Assignment_Year || '_' || Evaluation_ID);
NOTICE:  QUERY PLAN:

Aggregate  (cost=3D5992.94..5992.94 rows=3D1 width=3D0)
  ->  Seq Scan on border_shop_list  (cost=3D0.00..5991.69 rows=3D500 width=
=3D0)
        SubPlan
          ->  Index Scan using sample_foreign_key on sample  (cost=3D0.00..=
5.97 rows=3D1 width=3D34)

EXPLAIN
------=_NextPart_000_000F_01C30EEB.9F203F60--