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

Re: Very slow query - why?

From: Eric Ridge <ebr(at)tcdi(dot)com>
To: "Ben" <reply(at)to-the-newsgroup(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Very slow query - why?
Date: 2004-02-01 01:56:14
Message-ID: D14C2333-5459-11D8-966F-000A95BB5944@tcdi.com (view raw or flat)
Thread:
Lists: 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


In response to

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2004-02-01 02:08:15
Subject: Re: select ... distinct performance
Previous:From: Tom LaneDate: 2004-02-01 01:53:42
Subject: Re: Problem with function

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