Very slow query - why?
От | Ben |
---|---|
Тема | Very slow query - why? |
Дата | |
Msg-id | ae9cafe25e3d05d6b9f2e1198c8beafa@news.teranews.com обсуждение исходный текст |
Ответы |
Re: Very slow query - why?
(Eric Ridge <ebr@tcdi.com>)
Re: Very slow query - why? (Richard Schilling <rschi@rsmba.biz>) |
Список | 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
В списке pgsql-general по дате отправления: