Hello list,
I'm having a hard time understanding why my query is not using my
indices when I change a field in the WHERE clause.
Please could someone shed some light on this.
Below is both queries and explain analyze for the different queries.
Regards,
Henrik
QUERY 1. Using WHERE tbl_file_structure_id.fk_file_id = 1307249.
SELECT max(pk_file_structure_id) as pk_object_id, max(fk_archive_id)
AS fk_archive_id, file_name AS
object_name, structure_path AS object_path, computer_name,
file_ctime AS object_ctime, pk_computer_id, filetype_icon AS
object_img, 'file' AS object_type, share_name, share_path, fk_file_id
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
archive_complete = true AND fk_file_id = 1307249 GROUP BY file_name,
file_ctime, structure_path,
pk_computer_id, filetype_icon, computer_name, share_name,
share_path, fk_file_id ORDER BY object_name LIMIT 20 OFFSET 0
EXPLAIN FOR QUERY 1. As you can see uses INDEX SCAN on several tables.
Limit (cost=8530.59..8531.49 rows=20 width=173) (actual
time=1.938..1.942 rows=1 loops=1)
-> GroupAggregate (cost=8530.59..8540.54 rows=221 width=173)
(actual time=1.932..1.933 rows=1 loops=1)
-> Sort (cost=8530.59..8531.14 rows=221 width=173) (actual
time=1.762..1.787 rows=20 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,
tbl_file_structure.fk_file_id
-> Hash Join (cost=4.41..8521.99 rows=221 width=173)
(actual time=0.655..1.460 rows=20 loops=1)
Hash Cond: (tbl_structure.fk_archive_id =
tbl_archive.pk_archive_id)
-> Nested Loop (cost=0.00..8514.54 rows=221
width=140) (actual time=0.184..0.904 rows=20 loops=1)
-> Nested Loop (cost=0.00..820.46
rows=67 width=140) (actual time=0.154..0.558 rows=10 loops=1)
-> Nested Loop (cost=0.00..280.40
rows=67 width=58) (actual time=0.123..0.287 rows=10 loops=1)
-> Nested Loop
(cost=0.00..9.73 rows=1 width=42) (actual time=0.087..0.179 rows=1
loops=1)
Join Filter:
(tbl_filetype.pk_filetype_id = tbl_file.fk_filetype_id)
-> Index Scan using
tbl_file_pkey on tbl_file (cost=0.00..8.28 rows=1 width=40) (actual
time=0.053..0.056 rows=1 loops=1)
Index Cond:
(1307249 = pk_file_id)
-> Seq Scan on
tbl_filetype (cost=0.00..1.20 rows=20 width=18) (actual
time=0.012..0.036 rows=20 loops=1)
-> Index Scan using
tbl_file_structure_idx on tbl_file_structure (cost=0.00..270.00
rows=67 width=24) (actual time=0.030..0.073 rows=10 loops=1)
Index Cond: (fk_file_id
= 1307249)
-> Index Scan using
tbl_structure_pkey on tbl_structure (cost=0.00..8.05 rows=1
width=98) (actual time=0.018..0.020 rows=1 loops=10)
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..114.29 rows=44 width=8) (actual
time=0.021..0.025 rows=2 loops=10)
Index Cond:
(tbl_acl.fk_file_structure_id = tbl_file_structure.pk_file_structure_id)
-> Hash (cost=4.00..4.00 rows=33 width=41)
(actual time=0.444..0.444 rows=33 loops=1)
-> Hash Join (cost=2.21..4.00 rows=33
width=41) (actual time=0.134..0.343 rows=33 loops=1)
Hash Cond: (tbl_archive.fk_share_id
= tbl_share.pk_share_id)
-> Seq Scan on tbl_archive
(cost=0.00..1.33 rows=33 width=16) (actual time=0.018..0.086 rows=33
loops=1)
Filter: archive_complete
-> Hash (cost=2.16..2.16 rows=4
width=41) (actual time=0.097..0.097 rows=4 loops=1)
-> Hash Join
(cost=1.07..2.16 rows=4 width=41) (actual time=0.058..0.082 rows=4
loops=1)
Hash Cond:
(tbl_share.fk_computer_id = tbl_computer.pk_computer_id)
-> Seq Scan on
tbl_share (cost=0.00..1.04 rows=4 width=29) (actual
time=0.007..0.013 rows=4 loops=1)
-> Hash
(cost=1.03..1.03 rows=3 width=20) (actual time=0.031..0.031 rows=3
loops=1)
-> Seq Scan on
tbl_computer (cost=0.00..1.03 rows=3 width=20) (actual
time=0.009..0.015 rows=3 loops=1)
Total runtime: 2.534 ms
========================================================================
==============
========================================================================
==============
QUERY 2. Using WHERE tbl_file.fk_filetype_id = 83.
SELECT max(pk_file_structure_id) as pk_object_id, max(fk_archive_id)
AS fk_archive_id, file_name AS
object_name, structure_path AS object_path, computer_name,
file_ctime AS object_ctime, pk_computer_id, filetype_icon AS
object_img, 'file' AS object_type, share_name, share_path, fk_file_id
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
archive_complete = true AND fk_filetype_id = 83 GROUP BY file_name,
file_ctime, structure_path,
pk_computer_id, filetype_icon, computer_name, share_name,
share_path, fk_file_id ORDER BY object_name LIMIT 20 OFFSET 0
EXPLAIN FOR QUERY 2. As you can see uses Bitmap Heap Scan on
tbl_file.fk_filetype_id BUT SEQ SCAN on all other tables.
The query cost speaks for itself.
Limit (cost=150979.11..150980.01 rows=20 width=173) (actual
time=25329.586..25330.167 rows=20 loops=1)
-> GroupAggregate (cost=150979.11..152886.93 rows=42396
width=173) (actual time=25329.580..25330.118 rows=20 loops=1)
-> Sort (cost=150979.11..151085.10 rows=42396 width=173)
(actual time=25329.520..25329.614 rows=73 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,
tbl_file_structure.fk_file_id
-> Nested Loop (cost=42435.52..144024.63 rows=42396
width=173) (actual time=8199.229..25311.927 rows=730 loops=1)
-> Seq Scan on tbl_filetype (cost=0.00..1.25
rows=1 width=18) (actual time=0.132..0.153 rows=1 loops=1)
Filter: (83 = pk_filetype_id)
-> Hash Join (cost=42435.52..143599.42
rows=42396 width=171) (actual time=8199.077..25309.420 rows=730 loops=1)
Hash Cond: (tbl_structure.fk_archive_id =
tbl_archive.pk_archive_id)
-> Hash Join (cost=42431.11..143012.07
rows=42396 width=138) (actual time=8198.468..25305.206 rows=730 loops=1)
Hash Cond:
(tbl_file_structure.fk_structure_id = tbl_structure.pk_structure_id)
-> Hash Join
(cost=31898.88..128061.91 rows=42396 width=56) (actual
time=5947.642..22602.311 rows=730 loops=1)
Hash Cond:
(tbl_acl.fk_file_structure_id = tbl_file_structure.pk_file_structure_id)
-> Seq Scan on tbl_acl
(cost=0.00..79136.22 rows=4427422 width=8) (actual
time=0.092..8887.385 rows=4427422 loops=1)
-> Hash
(cost=31738.31..31738.31 rows=12846 width=56) (actual
time=5801.353..5801.353 rows=330 loops=1)
-> Hash Join
(cost=2094.84..31738.31 rows=12846 width=56) (actual
time=292.297..5798.486 rows=330 loops=1)
Hash Cond:
(tbl_file_structure.fk_file_id = tbl_file.pk_file_id)
-> Seq Scan on
tbl_file_structure (cost=0.00..24484.55 rows=1341455 width=24)
(actual time=13.953..3090.936 rows=1341455 loops=1)
-> Hash
(cost=2079.20..2079.20 rows=1251 width=40) (actual
time=180.500..180.500 rows=112 loops=1)
-> Bitmap
Heap Scan on tbl_file (cost=25.96..2079.20 rows=1251 width=40)
(actual time=22.210..180.109 rows=112 loops=1)
Recheck Cond: (fk_filetype_id = 83)
->
Bitmap Index Scan on tbl_file_idx6 (cost=0.00..25.65 rows=1251
width=0) (actual time=21.958..21.958 rows=112 loops=1)
Index Cond: (fk_filetype_id = 83)
-> Hash (cost=5576.88..5576.88
rows=176188 width=98) (actual time=1859.992..1859.992 rows=176188
loops=1)
-> Seq Scan on tbl_structure
(cost=0.00..5576.88 rows=176188 width=98) (actual
time=7.321..1062.527 rows=176188 loops=1)
-> Hash (cost=4.00..4.00 rows=33
width=41) (actual time=0.574..0.574 rows=33 loops=1)
-> Hash Join (cost=2.21..4.00
rows=33 width=41) (actual time=0.264..0.474 rows=33 loops=1)
Hash Cond:
(tbl_archive.fk_share_id = tbl_share.pk_share_id)
-> Seq Scan on tbl_archive
(cost=0.00..1.33 rows=33 width=16) (actual time=0.054..0.122 rows=33
loops=1)
Filter: archive_complete
-> Hash (cost=2.16..2.16
rows=4 width=41) (actual time=0.190..0.190 rows=4 loops=1)
-> Hash Join
(cost=1.07..2.16 rows=4 width=41) (actual time=0.148..0.170 rows=4
loops=1)
Hash Cond:
(tbl_share.fk_computer_id = tbl_computer.pk_computer_id)
-> Seq Scan on
tbl_share (cost=0.00..1.04 rows=4 width=29) (actual
time=0.049..0.055 rows=4 loops=1)
-> Hash
(cost=1.03..1.03 rows=3 width=20) (actual time=0.073..0.073 rows=3
loops=1)
-> Seq Scan
on tbl_computer (cost=0.00..1.03 rows=3 width=20) (actual
time=0.050..0.058 rows=3 loops=1)
Total runtime: 25330.924 ms
