Re: Very slow query - why?

Looking at your query, I notice that the longer query happens when you
search on b.zcustnum=30538 while the LEFT OUTER JOIN remains the same.
It could be that when testing a.zcustnum=30530 the server can short
circuit the logic - it only has to check a.zcustnum to see if the
entire tuple should be selected. On the other hand when its selecting
on b.zcustnum, it has to go match up a.zcustnum with b.zcustnum first
before testing for the condition. Extra processing. Try clustering on
field zcustnum to see if that helps, or when selecting only on
b.zcustnum you might make b the first table specified in your join.

You might also look at clustering zcustnum in both tables.

Richard

On 2004.01.28 14:20 Ben wrote:
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>