Re: Very slow query - why? - Mailing list pgsql-general

On Jan 28, 2004, at 5:20 PM, 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.

using the EXPLAIN command would probably open your eyes to where the
problem is.

In addition, if this zcustnum column is a bigint, you need to either
quote the rhs of the filter or cast it to a bitint in order for
postgres to actually use the index you have.

SELECT ... WHERE zcustnum = '30538'
       or
SELECT ... WHERE zcustnum = 30538::bigint;

eric

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