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> (raw)
In-Reply-To: <18324.1051657553@sss.pgh.pa.us>
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
Attachments:NewQuery.txt (text/plain, 1.8K) download
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)
7.3.2
neo=# explain analyze 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);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2460448.75..2460448.75 rows=1 width=0) (actual time=16500.14..16500.14 rows=1 loops=1)
-> Seq Scan on border_shop_list (cost=0.00..2460447.50 rows=500 width=0) (actual time=3245.59..16500.11 rows=3 loops=1)
Filter: (subplan)
SubPlan
-> Materialize (cost=2460.41..2460.41 rows=2741 width=34) (actual time=0.02..0.45 rows=1533 loops=3065)
-> Seq Scan on sample (cost=0.00..2460.41 rows=2741 width=34) (actual time=41.89..50.75 rows=3062 loops=1)
Filter: (foreign_key IS NOT NULL)
Total runtime: 16500.83 msec
(8 rows)
7.2.1
neo=# explain 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);
NOTICE: QUERY PLAN:
Aggregate (cost=11323743.75..11323743.75 rows=1 width=0)
-> Seq Scan on border_shop_list (cost=0.00..11323742.50 rows=500 width=0)
SubPlan
-> Seq Scan on sample (cost=0.00..22647.41 rows=33457 width=34)
EXPLAIN
OldQuery.txt (text/plain, 1.7K) download
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)
7.3.2
neo=# explain analyze 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);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3959148.75..3959148.75 rows=1 width=0) (actual time=667055.64..667055.64 rows=1 loops=1)
-> Seq Scan on border_shop_list (cost=0.00..3959147.50 rows=500 width=0) (actual time=110283.60..667055.60 rows=3 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on sample (cost=0.00..3959.13 rows=1 width=34) (actual time=217.62..217.62 rows=1 loops=3065)
Filter: ((foreign_key)::text = ((((('Quantum_'::text || ($0)::text) || '_'::text) || ($1)::text) || '_'::text) || ($2)::text))
Total runtime: 667055.79 msec
(7 rows)
7.2.1
neo=# explain 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);
NOTICE: QUERY PLAN:
Aggregate (cost=5992.94..5992.94 rows=1 width=0)
-> Seq Scan on border_shop_list (cost=0.00..5991.69 rows=500 width=0)
SubPlan
-> Index Scan using sample_foreign_key on sample (cost=0.00..5.97 rows=1 width=34)
EXPLAIN
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pdarley@kinesis-cem.com, pgsql-performance@postgresql.org
Subject: Re: Query Plan far worse in 7.3.2 than 7.2.1
In-Reply-To: <NNEAICKPNOGDBHNCEDCPIEHGDMAA.pdarley@kinesis-cem.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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