Re: Very slow query - why?
От | Eric Ridge |
---|---|
Тема | Re: Very slow query - why? |
Дата | |
Msg-id | D14C2333-5459-11D8-966F-000A95BB5944@tcdi.com обсуждение исходный текст |
Ответ на | Very slow query - why? ("Ben" <reply@to-the-newsgroup.com>) |
Список | 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
В списке pgsql-general по дате отправления: