Hi,
I'm hoping someone can offer some help here. The query and explain analyze and
table layout are below and attached in a text file if the formatting is bad.
The query is part of a bigger query that our front end runs. This is the part
that takes forever (84 minutes in this case) to finish and more often than not
the front end times out. The table (answerselectinstance) has 168664317 rows
while the member table has 626435 rows.
Postgres Version 8.25
CentOs 5.2
16 Gig RAM
192MB work_mem (increasing to 400MB didn't change the outcome)
very light use on this server, it ais a slave to a slony replicated
master/slave setup.
Again, apologies if the formatting got munged, the attached text file has the
same info.
Thanking you in advance for any help and suggestions.
Aaron
explain analyze select distinct(id) from member where id in (select memberid
from answerselectinstance where nswerid = 127443 OR answerid = 127444 OR
answerid = 127445 OR answerid = 127446 OR answerid = 127447 OR answerid =
127448 ) ;
LOG: duration: 5076038.709 ms statement: explain analyze select distinct(id)
from member where id in (select memberid from answerselectinstance where
answerid = 127443 OR answerid = 127444 OR answerid = 127445 OR answerid =
127446 OR answerid = 127447 OR answerid = 127448 ) ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=101357.24..101357.28 rows=9 width=4) (actual
time=5075511.974..5075911.077 rows=143520 loops=1)
-> Sort (cost=101357.24..101357.26 rows=9 width=4) (actual
time=5075511.971..5075644.323 rows=143520 loops=1)
Sort Key: member.id
-> Nested Loop IN Join (cost=0.00..101357.10 rows=9 width=4) (actual
time=19.867..5075122.724 rows=143520 loops=1)
-> Seq Scan on member (cost=0.00..78157.65 rows=626265
width=4) (actual time=3.338..2003.582 rows=626410 loops=1)
-> Index Scan using asi_memberid_idx on answerselectinstance
(cost=0.00..444.46 rows=9 width=4) (actual time=8.096..8.096 rows=0
loops=626410)
Index Cond: (member.id = answerselectinstance.memberid)
Filter: ((answerid = 127443) OR (answerid = 127444) OR
(answerid = 127445) OR (answerid = 127446) OR (answerid = 127447) OR (answerid
= 127448))
Total runtime: 5076034.203 ms
(9 rows)
Column | Type |
Modifiers
----------------+-----------------------------+------------------------------------------------------------
memberid | integer | not null
answerid | integer | not null
taskinstanceid | integer | not null default 0
created | timestamp without time zone | default "timestamp"('now'::text)
id | integer | not null default
nextval(('"asi_id_seq"'::text)::regclass)
Indexes:
"asi_pkey" PRIMARY KEY, btree (id)
"asi_answerid_idx" btree (answerid)
"asi_memberid_idx" btree (memberid)
"asi_taskinstanceid_idx" btree (taskinstanceid)
Triggers:
_bzzprod_cluster_denyaccess_301 BEFORE INSERT OR DELETE OR UPDATE ON
answerselectinstance FOR EACH ROW EXECUTE PROCEDURE
_bzzprod_cluster.denyaccess('_bzzprod_cluster')
explain analyze select distinct(id) from member where id in (select memberid
from answerselectinstance where nswerid = 127443 OR answerid = 127444 OR
answerid = 127445 OR answerid = 127446 OR answerid = 127447 OR answerid =
127448 ) ;
LOG: duration: 5076038.709 ms statement: explain analyze select distinct(id)
from member where id in (select memberid from answerselectinstance where
answerid = 127443 OR answerid = 127444 OR answerid = 127445 OR answerid =
127446 OR answerid = 127447 OR answerid = 127448 ) ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=101357.24..101357.28 rows=9 width=4) (actual
time=5075511.974..5075911.077 rows=143520 loops=1)
-> Sort (cost=101357.24..101357.26 rows=9 width=4) (actual
time=5075511.971..5075644.323 rows=143520 loops=1)
Sort Key: member.id
-> Nested Loop IN Join (cost=0.00..101357.10 rows=9 width=4) (actual
time=19.867..5075122.724 rows=143520 loops=1)
-> Seq Scan on member (cost=0.00..78157.65 rows=626265
width=4) (actual time=3.338..2003.582 rows=626410 loops=1)
-> Index Scan using asi_memberid_idx on answerselectinstance
(cost=0.00..444.46 rows=9 width=4) (actual time=8.096..8.096 rows=0
loops=626410)
Index Cond: (member.id = answerselectinstance.memberid)
Filter: ((answerid = 127443) OR (answerid = 127444) OR
(answerid = 127445) OR (answerid = 127446) OR (answerid = 127447) OR (answerid
= 127448))
Total runtime: 5076034.203 ms
(9 rows)
Column | Type |
Modifiers
----------------+-----------------------------+------------------------------------------------------------
memberid | integer | not null
answerid | integer | not null
taskinstanceid | integer | not null default 0
created | timestamp without time zone | default "timestamp"('now'::text)
id | integer | not null default
nextval(('"asi_id_seq"'::text)::regclass)
Indexes:
"asi_pkey" PRIMARY KEY, btree (id)
"asi_answerid_idx" btree (answerid)
"asi_memberid_idx" btree (memberid)
"asi_taskinstanceid_idx" btree (taskinstanceid)
Triggers:
_bzzprod_cluster_denyaccess_301 BEFORE INSERT OR DELETE OR UPDATE ON
answerselectinstance FOR EACH ROW EXECUTE PROCEDURE
_bzzprod_cluster.denyaccess('_bzzprod_cluster')
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
