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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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