Hello list,
I'm using pgsql 8.2.4 and I have this query which is sooo slow but I
can seem to figure out why.
It is using the appropriate indexes but there is something wrong with
the nested loops.
I'm running auto vacuum so the statistics should be up to date. I've
increase the statistics on most foreign keys.
Below is the query and the explain analyze.
If you need more info please let me know.
Thanks,
henke
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 ( LOWER(file_name) LIKE LOWER('awstats%') AND archive_complete
= true) GROUP BY file_name, file_ctime, structure_path,
pk_computer_id, filetype_icon, computer_name, share_name, share_path)
"HashAggregate (cost=180.28..180.54 rows=21 width=125) (actual
time=144071.947..144180.311 rows=7794 loops=1)"
" -> Nested Loop (cost=11.29..179.81 rows=21 width=125) (actual
time=1.519..139469.148 rows=224606 loops=1)"
" -> Hash Join (cost=11.29..144.82 rows=4 width=133) (actual
time=1.356..81072.074 rows=40836 loops=1)"
" Hash Cond: (tbl_share.fk_computer_id =
tbl_computer.pk_computer_id)"
" -> Hash Join (cost=9.15..142.63 rows=4 width=122)
(actual time=1.117..80482.340 rows=40836 loops=1)"
" Hash Cond: (tbl_archive.fk_share_id =
tbl_share.pk_share_id)"
" -> Nested Loop (cost=8.02..141.43 rows=6
width=108) (actual time=0.979..79847.699 rows=40836 loops=1)"
" -> Nested Loop (cost=4.73..97.56 rows=6
width=108) (actual time=0.812..69005.311 rows=43946 loops=1)"
" -> Nested Loop (cost=4.73..58.09
rows=6 width=50) (actual time=0.662..50552.977 rows=43946 loops=1)"
" -> Nested Loop
(cost=0.01..9.84 rows=1 width=42) (actual time=0.409..7371.172
rows=25039 loops=1)"
" Join Filter:
(tbl_filetype.pk_filetype_id = tbl_file.fk_filetype_id)"
" -> Index Scan using
tbl_file_idx on tbl_file (cost=0.01..8.34 rows=1 width=39) (actual
time=0.283..3339.003 rows=25039 loops=1)"
" Index Cond: ((lower
((file_name)::text) ~>=~ 'awstats'::character varying) AND (lower
((file_name)::text) ~<~ 'awstatt'::character varying))"
" Filter: (lower
((file_name)::text) ~~ 'awstats%'::text)"
" -> Seq Scan on
tbl_filetype (cost=0.00..1.22 rows=22 width=19) (actual
time=0.013..0.072 rows=22 loops=25039)"
" -> Bitmap Heap Scan on
tbl_file_structure (cost=4.72..48.12 rows=11 width=24) (actual
time=0.964..1.685 rows=2 loops=25039)"
" Recheck Cond:
(tbl_file.pk_file_id = tbl_file_structure.fk_file_id)"
" -> Bitmap Index Scan on
tbl_file_structure_idx (cost=0.00..4.72 rows=11 width=0) (actual
time=0.322..0.322 rows=2 loops=25039)"
" 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.56 rows=1
width=74) (actual time=0.391..0.401 rows=1 loops=43946)"
" Index Cond:
(tbl_structure.pk_structure_id = tbl_file_structure.fk_structure_id)"
" -> Bitmap Heap Scan on tbl_archive
(cost=3.29..7.30 rows=1 width=16) (actual time=0.217..0.221 rows=1
loops=43946)"
" Recheck Cond:
(tbl_archive.pk_archive_id = tbl_structure.fk_archive_id)"
" Filter: archive_complete"
" -> Bitmap Index Scan on
tbl_archive_pkey (cost=0.00..3.29 rows=1 width=0) (actual
time=0.133..0.133 rows=64 loops=43946)"
" Index Cond:
(tbl_archive.pk_archive_id = tbl_structure.fk_archive_id)"
" -> Hash (cost=1.06..1.06 rows=6 width=30)
(actual time=0.094..0.094 rows=6 loops=1)"
" -> Seq Scan on tbl_share
(cost=0.00..1.06 rows=6 width=30) (actual time=0.053..0.067 rows=6
loops=1)"
" -> Hash (cost=2.06..2.06 rows=6 width=19) (actual
time=0.199..0.199 rows=6 loops=1)"
" -> Seq Scan on tbl_computer (cost=0.00..2.06
rows=6 width=19) (actual time=0.109..0.167 rows=6 loops=1)"
" -> Index Scan using tbl_acl_idx on tbl_acl
(cost=0.00..8.54 rows=17 width=8) (actual time=1.205..1.380 rows=6
loops=40836)"
" Index Cond: (tbl_acl.fk_file_structure_id =
tbl_file_structure.pk_file_structure_id)"
"Total runtime: 144215.020 ms"
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
