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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Problem with API (libpq) - detailed error codes
Следующее
От: "Alberto Mariani"
Дата:
Сообщение: How to...