intersect performance (PG 7.1.3 vs 7.2)
От | d_nardini@btconnect.com |
---|---|
Тема | intersect performance (PG 7.1.3 vs 7.2) |
Дата | |
Msg-id | 0CBD3E12-48E6-11D6-8010-0030654E696C@btconnect.com обсуждение исходный текст |
Ответы |
Re: intersect performance (PG 7.1.3 vs 7.2)
|
Список | pgsql-sql |
Hi, I'm running the following statement on a (development) Mac OS X (PG 7.2) box and on a (production) RedHat 7.0 (PG 7.1.3) box. On the OS X it's performing very well ... on RH it's VERY SLOW, with CPU utilization hitting 90%+ (on OS X it hardly exceeds 2%). BTW - replacing 'intersect' with 'union' in the SQL behaves the same. (RH 7.0 box has 4x the amount of memory and CPU power - plenty of spare capacity). Do I need to upgrade the RH box with 7.2 (have there been significant enhancements in this area ?), or have I missed something obvious ? Any comments / advice welcome ! SQL statement : select i.master_reference from image i, image_keyword ik, keyword k where k.keyword = 'pen' and ik.keyword_id = k.keyword_id and i.image_id = ik.image_id intersect select i.master_reference from image i, image_keyword ik, keyword k where k.keyword = 'cheque' and ik.keyword_id = k.keyword_id and i.image_id = ik.image_id intersect select i.master_reference from image i, image_keyword ik, keyword k where k.keyword = 'purchase' and ik.keyword_id = k.keyword_id and i.image_id = ik.image_id order by master_reference limit 12, 0; DEFINITIONS statements : drop table image; drop sequence image_seq; create sequence image_seq; create table image ( image_id bigint not null default nextval('image_seq') primary key, artist_id integer not null default'0' ); drop table image_keyword; create table image_keyword ( image_id bigint not null default '0', keyword_id bigint not null default '0' ); create index image_keyword_image_id_ix on image_keyword (image_id); create index image_keyword_keyword_id_ix on image_keyword (keyword_id); drop table keyword; drop sequence keyword_seq; create sequence keyword_seq; create table keyword ( keyword_id bigint not null default nextval('keyword_seq') primary key, keyword varchar(50) notnull unique ); create index keyword_keyword_ix on keyword (keyword); approx number of records/rows in the above tables : image = 15000 image_keyword = 600000 keyword = 40000
В списке pgsql-sql по дате отправления: