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
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 |