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

Query Plan far worse in 7.3.2 than 7.2.1

From: "Peter Darley" <pdarley(at)kinesis-cem(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Query Plan far worse in 7.3.2 than 7.2.1
Date: 2003-04-30 14:39:24
Message-ID: NNEAICKPNOGDBHNCEDCPIEHGDMAA.pdarley@kinesis-cem.com (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
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

Attachment: OldQuery.txt
Description: text/plain (1.7 KB)
Attachment: NewQuery.txt
Description: text/plain (1.8 KB)

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-04-30 14:54:27
Subject: Re: Query Plan far worse in 7.3.2 than 7.2.1
Previous:From: Tom LaneDate: 2003-04-30 14:22:25
Subject: Re: Why LIMIT after scanning the table?

pgsql-sql by date

Next:From: Tom LaneDate: 2003-04-30 14:54:27
Subject: Re: Query Plan far worse in 7.3.2 than 7.2.1
Previous:From: SZŰCS GáborDate: 2003-04-30 11:00:40
Subject: Re: Query Plan far worse in 7.3.2 than 7.2.1

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