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

Very slow query - why?

From: "Ben" <reply(at)to-the-newsgroup(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Very slow query - why?
Date: 2004-01-28 22:20:57
Message-ID: ae9cafe25e3d05d6b9f2e1198c8beafa@news.teranews.com (view raw or flat)
Thread:
Lists: pgsql-general
My question, pulled out of my source code comments:

// This search is SLOW when WHERE is just zcustnum. This is inexplicable
// to me, as the WHERE and ON conditions only reference zcustnum directly
// in both tables, and in both instances there are btree indexes for them.
// It seems to me that such a search ought to be almost instantaneous; the
// ordered lists of zcustnum in the indexes should make the ON and WHERE
// clauses absolutely trivial to evaluate. In momcust, zcustnum is unique
// (but not enforced by the database - I just never create a duplicate.)
// In sonaddr, zcustnum is not always unique. Basically, this reflects
// the fact that customers may store more than one address per account.
//
// Conditions:
// ===========
// ------------- initial portion of statement, formatted for readability:
// SELECT	a.zcustnum,trim(a.zcompany),trim(a.zfirstname),
//			trim(a.zlastname),trim(a.zaddr),trim(a.zaddr2),
//			trim(a.zcity),trim(a.zstate),trim(a.zzipcode),
//			trim(a.zcountry),a.zbigphone,a.zbigaltph,trim(a.zemail),
//			a.zanumb,trim(a.zsalu),trim(a.ztitle),a.zoptin,
//			b.zodr_date,b.zbadcheck,trim(b.zcomment),trim(b.zcomment2)
//
//			FROM sonaddr AS a
//			LEFT OUTER JOIN momcust AS b
//			ON (a.zcustnum = b.zcustnum)

// ------------- Alternate WHERE clause timings:
//	a:		WHERE a.zcustnum=30538							--  4 secs
//	b:		WHERE b.zcustnum=30538							-- 12 secs
//	c:		WHERE a.zcustnum=30538 AND b.zcustnum=30538		--  4 secs

// ------------- Table sizes:
// 101679 sonaddr records
// 102653 momcust records

// ------------- Host conditions:
// PostgreSQL 4.3
// Dell dual 3 GHz Pentium 4 CPU
// Linux 2.4.20 SMP

// ------------- Other information:
// Number of result rows returned for test: 6.
// Using libpq interface through c language, over a network connection.
// The following select is almost immediate, perhaps .1 second.
// An additional indexed field, a.znumb, is used in the WHERE clause.
// It returns one result. Why would this be so much faster?
// -------------
// SELECT	a.zcustnum,trim(a.zcompany),trim(a.zfirstname),
//			trim(a.zlastname),trim(a.zaddr),trim(a.zaddr2),
//			trim(a.zcity),trim(a.zstate),trim(a.zzipcode),
//			trim(a.zcountry),a.zbigphone,a.zbigaltph,trim(a.zemail),
//			a.zanumb,trim(a.zsalu),trim(a.ztitle),a.zoptin,
//			b.zodr_date,b.zbadcheck,trim(b.zcomment),trim(b.zcomment2),
//			b.znomail,trim(b.zwebsite),trim(b.zpassword),trim(b.zquery),
//			trim(b.zanswer),trim(b.zfirstname),trim(b.zlastname)
//
//			FROM sonaddr AS a
//			LEFT OUTER JOIN momcust AS b
//			ON (a.zcustnum = b.zcustnum)
//
//			WHERE a.zcustnum=30538 AND a.zanumb=3

Thanks for any insight

--Ben


Responses

pgsql-general by date

Next:From: Tom LaneDate: 2004-01-28 22:42:16
Subject: Re: 7.3.4 freezing
Previous:From: John Sidney-WoollettDate: 2004-01-28 22:14:58
Subject: Re: Specifying many rows in a table

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