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)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: 16 parameter limit
Следующее
От: "Josh Berkus"
Дата:
Сообщение: Re: 16 parameter limit