I have a weird query execution plan problem I am trying to debug on
Postgresql 8.2.6. I have a query that joins against a temporary table that
has very few rows. If the temporary table is analyzed before the query runs,
all is well. If the temporary table is not analyzed before running the query,
the execution plan chosen by Postgres is comprised entirely of sequential
scans, and the whole query can take many hours to execute.
In the application exhibiting this issue there is an "analyze tmpInstanceId"
coded right before executing this query. But sometimes PostgreSQL chooses to
execute the query as if the analyze has not been run, or its results
discarded, or I don't know why every once in a while this query goes off on
the all sequential scan plan. (I have put some debugging code in the
application, and when the query takes longer to execute than expected, I
print the explain right after the query finished executing, and the plan it
comes up with is the second one I'm including. When it's fast, it is the
first one)
My questions are:
- what would make the analyze operation "fail" in the eyes of the planner?
- why joining to a single unanalyzed table disables any and all indexes from
the other tables references in the query?
create temporary table tmpInstanceId(
idx serial primary key,
instanceId integer
);
create index tmpInstanceIdIdx on tmpInstanceId(instanceId);
insert into tmpInstanceId(instanceId) values (492121), (492125);
--analyze tmpInstanceId;
explain
--analyze
select tmpInstanceId.idx, Items.item, Versions.version,
Flavors.flavor, tt.flags, Nodes.timeStamps
from tmpInstanceId
join TroveTroves as tt using(instanceId)
join Instances on tt.includedId = Instances.instanceId
join Items on Instances.itemId = Items.itemId
join Versions on Instances.versionId = Versions.versionId
join Flavors on Instances.flavorId = Flavors.flavorId
join Nodes on
Instances.itemId = Nodes.itemId and
Instances.versionId = Nodes.versionId ;
explain analyze when analyzing the temporary table first:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=3258.50..28509.89 rows=217 width=333) (actual
time=359.328..671.078 rows=10571 loops=1)
-> Nested Loop (cost=3258.50..27812.10 rows=217 width=322) (actual
time=331.048..557.858 rows=10571 loops=1)
-> Hash Join (cost=3258.50..27112.73 rows=217 width=276) (actual
time=330.947..503.239 rows=10571 loops=1)
Hash Cond: (instances.flavorid = flavors.flavorid)
-> Nested Loop (cost=3222.89..27074.13 rows=217 width=45)
(actual time=329.451..489.734 rows=10571 loops=1)
-> Hash Join (cost=3222.89..14374.81 rows=3916 width=20)
(actual time=329.295..412.439 rows=10571 loops=1)
Hash Cond: (instances.instanceid = tt.includedid)
-> Seq Scan on instances (cost=0.00..9317.19
rows=478819 width=16) (actual time=0.037..185.202 rows=478819 loops=1)
-> Hash (cost=3173.94..3173.94 rows=3916 width=12)
(actual time=16.738..16.738 rows=10571 loops=1)
-> Nested Loop (cost=0.00..3173.94 rows=3916
width=12) (actual time=0.159..11.248 rows=10571 loops=1)
-> Seq Scan on tmpinstanceid
(cost=0.00..1.02 rows=2 width=8) (actual time=0.005..0.007 rows=2 loops=1)
-> Index Scan using
trovetrovesinstanceincluded_uq on trovetroves tt (cost=0.00..1561.99 rows=1958
width=12) (actual time=0.102..3.400 rows=5286 loops=2)
Index Cond:
(tmpinstanceid.instanceid = tt.instanceid)
-> Index Scan using nodesversionid_fk on nodes
(cost=0.00..3.23 rows=1 width=25) (actual time=0.005..0.006 rows=1 loops=10571)
Index Cond: ((instances.versionid = nodes.versionid)
AND (instances.itemid = nodes.itemid))
-> Hash (cost=28.05..28.05 rows=605 width=239) (actual
time=1.406..1.406 rows=605 loops=1)
-> Seq Scan on flavors (cost=0.00..28.05 rows=605
width=239) (actual time=0.062..0.744 rows=605 loops=1)
-> Index Scan using versions_pkey on versions (cost=0.00..3.21
rows=1 width=58) (actual time=0.003..0.004 rows=1 loops=10571)
Index Cond: (instances.versionid = versions.versionid)
-> Index Scan using items_pkey on items (cost=0.00..3.20 rows=1 width=23)
(actual time=0.009..0.010 rows=1 loops=10571)
Index Cond: (instances.itemid = items.itemid)
Total runtime: 673.460 ms
explain without analyzing the temporaray table first:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2380399.89..2448745.27 rows=210188 width=333)
Hash Cond: (instances.itemid = items.itemid)
-> Hash Join (cost=2379801.92..2443155.34 rows=210188 width=322)
Hash Cond: (instances.versionid = versions.versionid)
-> Hash Join (cost=2378320.77..2437733.16 rows=210188 width=276)
Hash Cond: (instances.flavorid = flavors.flavorid)
-> Hash Join (cost=2378285.15..2434807.46 rows=210188 width=45)
Hash Cond: (tmpinstanceid.instanceid = tt.instanceid)
-> Seq Scan on tmpinstanceid (cost=0.00..29.40 rows=1940
width=8)
-> Hash (cost=2260473.14..2260473.14 rows=5291201
width=45)
-> Hash Join (cost=95937.67..2260473.14
rows=5291201 width=45)
Hash Cond: (tt.includedid =
instances.instanceid)
-> Seq Scan on trovetroves tt
(cost=0.00..1753045.32 rows=95620632 width=12)
-> Hash (cost=95606.47..95606.47 rows=26496
width=41)
-> Merge Join (cost=89458.93..95606.47
rows=26496 width=41)
Merge Cond: ((instances.versionid
= nodes.versionid) AND (instances.itemid = nodes.itemid))
-> Sort (cost=54491.66..55688.71
rows=478819 width=16)
Sort Key:
instances.versionid, instances.itemid
-> Seq Scan on instances
(cost=0.00..9317.19 rows=478819 width=16)
-> Sort (cost=34967.27..35731.14
rows=305546 width=25)
Sort Key: nodes.versionid,
nodes.itemid
-> Seq Scan on nodes
(cost=0.00..7130.46 rows=305546 width=25)
-> Hash (cost=28.05..28.05 rows=605 width=239)
-> Seq Scan on flavors (cost=0.00..28.05 rows=605
width=239)
-> Hash (cost=946.07..946.07 rows=42807 width=58)
-> Seq Scan on versions (cost=0.00..946.07 rows=42807 width=58)
-> Hash (cost=354.65..354.65 rows=19465 width=23)
-> Seq Scan on items (cost=0.00..354.65 rows=19465 width=23)
(28 rows)
Thanks,
Cristian
--
Cristian Gafton
rPath, Inc.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
