Hi,
I have a query where I do not understand that the rows number that explain analyze finds differs so much from what explain estimates (3rd nested loop estimates 1 row but in real it is 4222 rows). I did analyze the tables (pgsql 7.4.1).
Here is the query:
explain analyze SELECT fts.val_1, max(fts.val_2) AS val_2
FROM docobjflat AS fts,
boxinfo,
docobjflat AS ftw0,
docobjflat AS ftw, envspec_mapWHERE boxinfo.member=158096693 AND boxinfo.envelope=ftw.envelope AND boxinfo.community=169964332 AND boxinfo.hide=FALSE AND ftw0.flatid=ftw.flatid AND fts.flatid=ftw.flatid AND fts.docstart=1 AND envspec_map.spec=169964482 AND envspec_map.community=boxinfo.community AND envspec_map.envelope=boxinfo.envelope
AND ftw0.val_14='IN-A01'
GROUP BY fts.val_1;
Query plan is attached.
Regards Dirk
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=134.58..134.58 rows=1 width=12) (actual time=1218.479..1218.480
rows=1 loops=1)
-> Nested Loop (cost=0.00..134.57 rows=1 width=12) (actual time=723.208..1218.167
rows=173 loops=1)
-> Nested Loop (cost=0.00..110.49 rows=1 width=42) (actual
time=0.687..636.649 rows=4222 loops=1)
-> Nested Loop (cost=0.00..86.39 rows=1 width=15) (actual
time=0.567..28.520 rows=4222 loops=1)
-> Nested Loop (cost=0.00..38.04 rows=1 width=8) (actual
time=0.394..6.078 rows=43 loops=1)
-> Index Scan using boxinfo_audi_index on boxinfo
(cost=0.00..16.89 rows=4 width=8) (actual time=0.190..2.791 rows=165 loops=1)
Index Cond: (member = 158096693::oid)
Filter: ((community = 169964332::oid) AND (hide =
false))
-> Index Scan using envspec_169964482_index on envspec_map
(cost=0.00..5.28 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=165)
Index Cond: ((envspec_map.envelope =
"outer".envelope) AND (envspec_map.community = 169964332::oid))
Filter: (spec = 169964482)
-> Index Scan using docobjflat_169964482_envelope on
docobjflat_169964482 ftw (cost=0.00..47.31 rows=83 width=19) (actual
time=0.049..0.291 rows=98 loops=43)
Index Cond: ("outer".envelope = ftw.envelope)
-> Index Scan using docobjflat_169964482_flatid on
docobjflat_169964482 fts (cost=0.00..24.07 rows=2 width=27) (actual time=0.010..0.138
rows=1 loops=4222)
Index Cond: (fts.flatid = "outer".flatid)
Filter: (docstart = 1)
-> Index Scan using docobjflat_169964482_flatid on docobjflat_169964482 ftw0
(cost=0.00..24.07 rows=1 width=15) (actual time=0.135..0.135 rows=0 loops=4222)
Index Cond: ("outer".flatid = ftw0.flatid)
Filter: (val_14 = 'IN-A01'::text)
Total runtime: 1219.200 ms
(20 rows)---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
