From: | John Aughey <jha(at)washucsc(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Unusual slowdown using subselects |
Date: | 2001-05-16 14:37:25 |
Message-ID: | Pine.BSF.4.21.0105160928150.56146-100000@washucsc.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm stress testing my application by creating large data sets. This
particular query selects rows from the schedule table that have a specific
owner_id. (I'll show you the results of explain)
calendar=# explain select * from schedule where schedule.owner_id=101 or
schedule.owner_id=102;
Index Scan using schedule_id_index, schedule_id_index on schedule
(cost=0.00..78.64 rows=20 width=40)
Looks great and executes very fast.
calendar=# explain select group_id from groups where
user_id=101;
NOTICE: QUERY PLAN:
Index Scan using groups_id_index on groups (cost=0.00..2.02 rows=1
width=4)
Again, very fast. The groups table maps users to groups.
However, this next one is slow.
calendar=# explain select * from schedule where schedule.owner_id in
(select group_id from groups where user_id=101);
NOTICE: QUERY PLAN:
Seq Scan on schedule (cost=0.00..2039895.00 rows=1000000 width=40)
SubPlan
-> Materialize (cost=2.02..2.02 rows=1 width=4)
-> Index Scan using groups_id_index on groups (cost=0.00..2.02
rows=1 width=4)
You'll see in this one, where the first example did a index scan, this one
with a very similar query does a seq scan. The two queries should be
nearly identical, but this one runs very slowly.
Can anyone explain why this happens and/or how I can do a sub-select like
this and get fast results?
Thank you
John Aughey
From | Date | Subject | |
---|---|---|---|
Next Message | Bill McGonigle | 2001-05-16 14:49:12 | Re: trouble with the automatic indexes on CREATE TABLE |
Previous Message | Vincent Roberts | 2001-05-16 14:27:41 | Function to detect trigger recursion |