Hello list,
I have a SELECT query that uses Seq scans instead of index scan
despite that the index scan is faster.
Below is the query and its first run with enable seqsan = true which
give a Seq Scan on tbl_structure (cost=0.00..19147.29 rows=172229
width=97) (actual time=0.094..878.309 rows=172229 loops=1). Total
query time 24116ms.
After that I turn off seqscans and now query time is 1257ms.
I've tried raising the statistics on pk_structure_id and
fk_structure_id but to no avail.
Regards,
henrik
=================== SETTING ENABLE_SEQSCAN = TRUE;
=========================
set enable_seqscan = true;
explain analyze SELECT COUNT(*) FROM tbl_file_structure
JOIN tbl_file ON pk_file_id = fk_file_id
JOIN tbl_structure ON pk_structure_id = fk_structure_id
JOIN tbl_archive ON pk_archive_id = fk_archive_id
JOIN tbl_share ON pk_share_id = fk_share_id
JOIN tbl_computer ON pk_computer_id = fk_computer_id
JOIN tbl_filetype ON pk_filetype_id = fk_filetype_id
JOIN tbl_acl ON fk_file_structure_id = pk_file_structure_id WHERE
( pk_file_id IN
(1595776,1595774,1595773,1595779,1615586,1595777,1595778,1614443,1695319
,
1695955,1695956,1587155,1695324,1616597,1614228,1695320,1695639,1696142,
1603869,1696202,1695848,1696203,1695412,1695561,1695562,1695563,1657822,
1694281,1693841,1585629,1696143,1694280,1693742,1694117,1589318,1695925,
1695849,1614442,1695584,1695540,1695541,1695542,1695539,1695691,1615887,
1615888,1695694,1695695,1696008,1659029,1694889,1695636,1695637,1695635,
1695633,1695634,1615737,1694888,1615886,1615889,1695536,1695693,1693814,
1693795,1695986,1657902,1696000,1603090,1611660,1696990,1585946,1696041,
1696042,1695261,1648536,1648539,1695850,1695869,1695873,1695871,1696328,
1696330,1696332,1589191,1696329,1589284,1696331,1696053,1696063,1696187,
1640756,1641544,1641695,1695985,1695344,1695415,1695531,1695594,1609809,
1695405,1615898,1695432,1695435,1695851,1692218,1658028,1695923,1695924,
1696054,1695365,1695433,1695436,1696882,1696229,1696230,1696231,1695544,
1658218,1694239,1693510,1697990,1615868,1695417,1598456,1695841,1696222,
1696087,1695604,1615864,1615860,1695762,1696045,1695874,1609716,1695631,
1695687,1695817,1615731,1615751,1695554,1695555,1695556,1695557,1695558,
1589560,1696223,1658096,1694048,1698519,1696064,1695380,1695518,1695434,
1696043,1696044,1695469,1695870,1695872,1696188,1695404,1695403,1695499,
1695842,1693566,1693375,1595775,1694879,1654126,1654134,1654128,1654124,
1654129,1654135,1654125,1654131,1654123,1654127,1696144,1694891,1695571,
1694887,1603749,1589686,1695407,1585638,1695449,1695524,1695523,1695533,
1654122,1654130,1657476,1658392,1693991,1596090,1596150,1690944,1690945,
1701473,1701817,1701914,1654133,1654076,1695625,1615725,1615920,1654132,
1654077,1654072,1693719,1654606,1692783,1694150,1596142,1654529,1696311,
1616047,1654409,1657157,1697755,1698044,1600214,1654136,1697019,1659100,
1694049,1591073,1698564,1694546,1694226,1693972,1693779,1658309,1698426,
1692830,1693894,1657308,1697795,1618611,1590802,1654620,1658097,1692757,
1697780,1698520,1693815,1693796,1655314,1655323,1694612,1693261,1697622,
1698008,1698302,1654668,1697770,1657198,1657229,1657244,1657286,1655797,
1694343,1697446,1600483,1655894,1603845,1696786,1695304)) AND
archive_complete = true GROUP BY file_name, file_ctime, structure_path,
pk_computer_id, filetype_icon, computer_name, share_name,
share_path;
"GroupAggregate (cost=115540.66..115968.69 rows=11414 width=148)
(actual time=2172.513..2412.807 rows=297 loops=1)"
" -> Sort (cost=115540.66..115569.20 rows=11414 width=148) (actual
time=2171.525..2340.522 rows=14769 loops=1)"
" Sort Key: tbl_file.file_name, tbl_file.file_ctime,
tbl_structure.structure_path, tbl_computer.pk_computer_id,
tbl_filetype.filetype_icon, tbl_computer.computer_name,
tbl_share.share_name, tbl_share.share_path"
" -> Nested Loop (cost=17179.08..113910.45 rows=11414
width=148) (actual time=104.599..1653.992 rows=14769 loops=1)"
" -> Hash Join (cost=17179.08..37262.58 rows=3161
width=156) (actual time=104.440..1404.388 rows=2787 loops=1)"
" Hash Cond: (tbl_structure.fk_archive_id =
tbl_archive.pk_archive_id)"
" -> Hash Join (cost=17056.35..37096.40
rows=3161 width=130) (actual time=103.952..1386.744 rows=2787 loops=1)"
" Hash Cond: (tbl_structure.pk_structure_id
= tbl_file_structure.fk_structure_id)"
" -> Seq Scan on tbl_structure
(cost=0.00..19147.29 rows=172229 width=97) (actual
time=0.094..878.309 rows=172229 loops=1)"
" -> Hash (cost=17016.84..17016.84
rows=3161 width=49) (actual time=101.803..101.803 rows=2787 loops=1)"
" -> Nested Loop
(cost=966.87..17016.84 rows=3161 width=49) (actual time=4.819..92.614
rows=2787 loops=1)"
" -> Hash Join
(cost=966.87..1998.83 rows=268 width=41) (actual time=4.651..13.958
rows=267 loops=1)"
" Hash Cond:
(tbl_file.fk_filetype_id = tbl_filetype.pk_filetype_id)"
" -> Bitmap Heap Scan on
tbl_file (cost=965.42..1993.69 rows=268 width=39) (actual
time=4.504..11.719 rows=267 loops=1)"
" Recheck Cond:
(pk_file_id = ANY
('{1595776,1595774,1595773,1595779,1615586,1595777,1595778,1614443,16953
19,1695955,1695956,1587155,1695324,1616597,1614228,1695320,1695639,16961
42,1603869,1696202,1695848,169 (..)"
" -> Bitmap Index
Scan on tbl_file_pkey (cost=0.00..965.35 rows=268 width=0) (actual
time=4.323..4.323 rows=267 loops=1)"
" Index Cond:
(pk_file_id = ANY
('{1595776,1595774,1595773,1595779,1615586,1595777,1595778,1614443,16953
19,1695955,1695956,1587155,1695324,1616597,1614228,1695320,1695639,16961
42,1603869,1696202,1695848 (..)"
" -> Hash
(cost=1.20..1.20 rows=20 width=18) (actual time=0.107..0.107 rows=20
loops=1)"
" -> Seq Scan on
tbl_filetype (cost=0.00..1.20 rows=20 width=18) (actual
time=0.014..0.053 rows=20 loops=1)"
" -> Index Scan using
tbl_file_structure_idx on tbl_file_structure (cost=0.00..55.79
rows=20 width=24) (actual time=0.059..0.258 rows=10 loops=267)"
" Index Cond:
(tbl_file.pk_file_id = tbl_file_structure.fk_file_id)"
" -> Hash (cost=122.60..122.60 rows=10 width=42)
(actual time=0.457..0.457 rows=10 loops=1)"
" -> Nested Loop (cost=2.05..122.60
rows=10 width=42) (actual time=0.112..0.423 rows=10 loops=1)"
" Join Filter:
(tbl_computer.pk_computer_id = tbl_share.fk_computer_id)"
" -> Nested Loop (cost=1.03..121.13
rows=10 width=29) (actual time=0.083..0.279 rows=10 loops=1)"
" Join Filter:
(tbl_share.pk_share_id = tbl_archive.fk_share_id)"
" -> Index Scan using
tbl_archive_pkey on tbl_archive (cost=0.00..119.42 rows=10 width=16)
(actual time=0.032..0.088 rows=10 loops=1)"
" Filter: archive_complete"
" -> Materialize
(cost=1.03..1.06 rows=3 width=29) (actual time=0.003..0.009 rows=3
loops=10)"
" -> Seq Scan on
tbl_share (cost=0.00..1.03 rows=3 width=29) (actual
time=0.011..0.018 rows=3 loops=1)"
" -> Materialize (cost=1.02..1.04
rows=2 width=21) (actual time=0.003..0.006 rows=2 loops=10)"
" -> Seq Scan on tbl_computer
(cost=0.00..1.02 rows=2 width=21) (actual time=0.010..0.015 rows=2
loops=1)"
" -> Index Scan using tbl_acl_idx on tbl_acl
(cost=0.00..24.17 rows=6 width=8) (actual time=0.057..0.069 rows=5
loops=2787)"
" Index Cond: (tbl_acl.fk_file_structure_id =
tbl_file_structure.pk_file_structure_id)"
"Total runtime: 2416.564 ms"
=================== SETTING ENABLE_SEQSCAN = FALSE;
=========================
set enable_seqscan = true;
Same query;
"GroupAggregate (cost=117765.67..118193.70 rows=11414 width=148)
(actual time=1123.587..1253.408 rows=297 loops=1)"
" -> Sort (cost=117765.67..117794.21 rows=11414 width=148) (actual
time=1123.066..1178.954 rows=14769 loops=1)"
" Sort Key: tbl_file.file_name, tbl_file.file_ctime,
tbl_structure.structure_path, tbl_computer.pk_computer_id,
tbl_filetype.filetype_icon, tbl_computer.computer_name,
tbl_share.share_name, tbl_share.share_path"
" -> Hash Join (cost=1099.94..116135.46 rows=11414
width=148) (actual time=6.128..546.573 rows=14769 loops=1)"
" Hash Cond: (tbl_structure.fk_archive_id =
tbl_archive.pk_archive_id)"
" -> Nested Loop (cost=965.42..115843.99 rows=11414
width=122) (actual time=5.091..481.176 rows=14769 loops=1)"
" -> Nested Loop (cost=965.42..39196.13
rows=3161 width=130) (actual time=5.048..242.904 rows=2787 loops=1)"
" -> Nested Loop (cost=965.42..17630.76
rows=3161 width=49) (actual time=4.901..108.275 rows=2787 loops=1)"
" -> Nested Loop
(cost=965.42..2612.75 rows=268 width=41) (actual time=4.714..20.976
rows=267 loops=1)"
" -> Bitmap Heap Scan on
tbl_file (cost=965.42..1993.69 rows=268 width=39) (actual
time=4.590..13.086 rows=267 loops=1)"
" Recheck Cond:
(pk_file_id = ANY
('{1595776,1595774,1595773,1595779,1615586,1595777,1595778,1614443,16953
19,1695955,1695956,1587155,1695324,1616597,1614228,1695320,1695639,16961
42,1603869,1696202,1695848,1696203,1 (..)"
" -> Bitmap Index Scan on
tbl_file_pkey (cost=0.00..965.35 rows=268 width=0) (actual
time=4.406..4.406 rows=267 loops=1)"
" Index Cond:
(pk_file_id = ANY
('{1595776,1595774,1595773,1595779,1615586,1595777,1595778,1614443,16953
19,1695955,1695956,1587155,1695324,1616597,1614228,1695320,1695639,16961
42,1603869,1696202,1695848,16962 (..)"
" -> Index Scan using
tbl_filetype_pkey on tbl_filetype (cost=0.00..2.30 rows=1 width=18)
(actual time=0.010..0.015 rows=1 loops=267)"
" Index Cond:
(tbl_filetype.pk_filetype_id = tbl_file.fk_filetype_id)"
" -> Index Scan using
tbl_file_structure_idx on tbl_file_structure (cost=0.00..55.79
rows=20 width=24) (actual time=0.065..0.288 rows=10 loops=267)"
" Index Cond:
(tbl_file.pk_file_id = tbl_file_structure.fk_file_id)"
" -> Index Scan using tbl_structure_pkey on
tbl_structure (cost=0.00..6.81 rows=1 width=97) (actual
time=0.039..0.042 rows=1 loops=2787)"
" Index Cond:
(tbl_structure.pk_structure_id = tbl_file_structure.fk_structure_id)"
" -> Index Scan using tbl_acl_idx on tbl_acl
(cost=0.00..24.17 rows=6 width=8) (actual time=0.055..0.067 rows=5
loops=2787)"
" Index Cond: (tbl_acl.fk_file_structure_id
= tbl_file_structure.pk_file_structure_id)"
" -> Hash (cost=134.40..134.40 rows=10 width=42)
(actual time=0.999..0.999 rows=10 loops=1)"
" -> Merge Join (cost=128.10..134.40 rows=10
width=42) (actual time=0.904..0.959 rows=10 loops=1)"
" Merge Cond: (tbl_computer.pk_computer_id =
tbl_share.fk_computer_id)"
" -> Index Scan using tbl_computer_pkey on
tbl_computer (cost=0.00..12.28 rows=2 width=21) (actual
time=0.176..0.180 rows=2 loops=1)"
" -> Sort (cost=128.10..128.13 rows=10
width=29) (actual time=0.711..0.722 rows=10 loops=1)"
" Sort Key: tbl_share.fk_computer_id"
" -> Merge Join (cost=119.59..127.94
rows=10 width=29) (actual time=0.617..0.662 rows=10 loops=1)"
" Merge Cond:
(tbl_share.pk_share_id = tbl_archive.fk_share_id)"
" -> Index Scan using
tbl_share_pkey on tbl_share (cost=0.00..12.30 rows=3 width=29)
(actual time=0.092..0.101 rows=3 loops=1)"
" -> Sort (cost=119.59..119.61
rows=10 width=16) (actual time=0.496..0.507 rows=10 loops=1)"
" Sort Key:
tbl_archive.fk_share_id"
" -> Index Scan using
tbl_archive_pkey on tbl_archive (cost=0.00..119.42 rows=10 width=16)
(actual time=0.098..0.456 rows=10 loops=1)"
" Filter:
archive_complete"
"Total runtime: 1257.126 ms"
