Hi list,
I'm having a weird acting query which simply retrieves some files
stored in a db which are related to a specific archive and also has a
size lower than 1024 bytes.
Explain analyze below. The first one is with seq-scan enabled and the
other one with seq-scans disabled. The weird thing is the seq scan on
tbl_file_structure and also the insane calculated cost of 100 000 000
on some tables.
Explain analyze below with both seq scan on and off.
Regards,
Henrik
EXPLAIN ANALYZE SELECT pk_file_id, file_name_in_tar, tar_name,
file_suffix, fk_tar_id, tar_compressed FROM tbl_file
INNER JOIN
tbl_file_structure ON fk_file_id = pk_file_id
INNER JOIN
tbl_structure ON fk_structure_id = pk_structure_id
LEFT OUTER JOIN tbl_tar ON fk_tar_id = pk_tar_id
WHERE file_indexed IS FALSE
AND file_copied IS TRUE
AND file_size < (1024)
AND LOWER
(file_suffix) IN(
SELECT LOWER
(filetype_suffix) FROM tbl_filetype_suffix WHERE
filetype_suffix_index IS TRUE
) AND fk_archive_id
= 115 ORDER BY fk_tar_id
"Sort (cost=238497.42..238499.49 rows=828 width=76) (actual
time=65377.316..65377.321 rows=5 loops=1)"
" Sort Key: tbl_file.fk_tar_id"
" -> Hash Left Join (cost=39935.64..238457.29 rows=828 width=76)
(actual time=61135.732..65377.246 rows=5 loops=1)"
" Hash Cond: (tbl_file.fk_tar_id = tbl_tar.pk_tar_id)"
" -> Hash Join (cost=39828.67..238336.86 rows=828 width=50)
(actual time=60776.587..65018.062 rows=5 loops=1)"
" Hash Cond: (tbl_file_structure.fk_structure_id =
tbl_structure.pk_structure_id)"
" -> Hash Join (cost=30975.39..228750.72 rows=72458
width=58) (actual time=14256.555..64577.950 rows=4650 loops=1)"
" Hash Cond: (tbl_file_structure.fk_file_id =
tbl_file.pk_file_id)"
" -> Seq Scan on tbl_file_structure
(cost=0.00..167417.09 rows=7902309 width=16) (actual
time=9.581..33702.852 rows=7801334 loops=1)"
" -> Hash (cost=30874.63..30874.63 rows=8061
width=50) (actual time=14058.396..14058.396 rows=486 loops=1)"
" -> Hash Join (cost=3756.16..30874.63
rows=8061 width=50) (actual time=9373.992..14056.119 rows=486 loops=1)"
" Hash Cond: (lower
((tbl_file.file_suffix)::text) = lower
((tbl_filetype_suffix.filetype_suffix)::text))"
" -> Bitmap Heap Scan on tbl_file
(cost=3754.47..29939.50 rows=136453 width=50) (actual
time=9068.525..13654.235 rows=154605 loops=1)"
" Recheck Cond: (file_size < 1024)"
" Filter: ((file_indexed IS
FALSE) AND (file_copied IS TRUE))"
" -> Bitmap Index Scan on
tbl_file_idx4 (cost=0.00..3720.36 rows=195202 width=0) (actual
time=9002.683..9002.683 rows=205084 loops=1)"
" Index Cond: (file_size <
1024)"
" -> Hash (cost=1.52..1.52 rows=14
width=8) (actual time=0.557..0.557 rows=14 loops=1)"
" -> HashAggregate
(cost=1.38..1.52 rows=14 width=8) (actual time=0.484..0.507 rows=14
loops=1)"
" -> Seq Scan on
tbl_filetype_suffix (cost=0.00..1.34 rows=14 width=8) (actual
time=0.383..0.423 rows=14 loops=1)"
" Filter:
(filetype_suffix_index IS TRUE)"
" -> Hash (cost=8778.54..8778.54 rows=5979 width=8)
(actual time=419.491..419.491 rows=11420 loops=1)"
" -> Bitmap Heap Scan on tbl_structure
(cost=617.08..8778.54 rows=5979 width=8) (actual
time=114.501..393.685 rows=11420 loops=1)"
" Recheck Cond: (fk_archive_id = 115)"
" -> Bitmap Index Scan on
tbl_structure_idx3 (cost=0.00..615.59 rows=5979 width=0) (actual
time=100.939..100.939 rows=11420 loops=1)"
" Index Cond: (fk_archive_id = 115)"
" -> Hash (cost=64.21..64.21 rows=3421 width=34) (actual
time=359.043..359.043 rows=3485 loops=1)"
" -> Seq Scan on tbl_tar (cost=0.00..64.21 rows=3421
width=34) (actual time=19.287..348.237 rows=3485 loops=1)"
"Total runtime: 65378.552 ms"
Now I disabled seq scans.
set enable_seqscan=false;
"Merge Left Join (cost=100331398.53..100331526.36 rows=828 width=76)
(actual time=36206.575..36291.847 rows=5 loops=1)"
" Merge Cond: (tbl_file.fk_tar_id = tbl_tar.pk_tar_id)"
" -> Sort (cost=100331398.53..100331400.60 rows=828 width=50)
(actual time=36030.473..36030.487 rows=5 loops=1)"
" Sort Key: tbl_file.fk_tar_id"
" -> Hash Join (cost=100012609.44..100331358.40 rows=828
width=50) (actual time=27279.046..36030.399 rows=5 loops=1)"
" Hash Cond: (tbl_file_structure.fk_structure_id =
tbl_structure.pk_structure_id)"
" -> Nested Loop (cost=100003756.16..100321772.87
rows=72397 width=58) (actual time=13225.815..35533.414 rows=4650
loops=1)"
" -> Hash Join (cost=100003756.16..100030874.63
rows=8061 width=50) (actual time=12888.880..19845.110 rows=486 loops=1)"
" Hash Cond: (lower
((tbl_file.file_suffix)::text) = lower
((tbl_filetype_suffix.filetype_suffix)::text))"
" -> Bitmap Heap Scan on tbl_file
(cost=3754.47..29939.50 rows=136453 width=50) (actual
time=12747.478..19266.843 rows=154605 loops=1)"
" Recheck Cond: (file_size < 1024)"
" Filter: ((file_indexed IS FALSE) AND
(file_copied IS TRUE))"
" -> Bitmap Index Scan on
tbl_file_idx4 (cost=0.00..3720.36 rows=195202 width=0) (actual
time=12689.593..12689.593 rows=205084 loops=1)"
" Index Cond: (file_size < 1024)"
" -> Hash (cost=100000001.52..100000001.52
rows=14 width=8) (actual time=0.313..0.313 rows=14 loops=1)"
" -> HashAggregate
(cost=100000001.38..100000001.52 rows=14 width=8) (actual
time=0.230..0.254 rows=14 loops=1)"
" -> Seq Scan on
tbl_filetype_suffix (cost=100000000.00..100000001.34 rows=14
width=8) (actual time=0.133..0.176 rows=14 loops=1)"
" Filter:
(filetype_suffix_index IS TRUE)"
" -> Index Scan using tbl_file_structure_idx on
tbl_file_structure (cost=0.00..35.82 rows=21 width=16) (actual
time=7.031..32.178 rows=10 loops=486)"
" Index Cond: (tbl_file_structure.fk_file_id
= tbl_file.pk_file_id)"
" -> Hash (cost=8778.54..8778.54 rows=5979 width=8)
(actual time=445.799..445.799 rows=11420 loops=1)"
" -> Bitmap Heap Scan on tbl_structure
(cost=617.08..8778.54 rows=5979 width=8) (actual
time=155.046..419.676 rows=11420 loops=1)"
" Recheck Cond: (fk_archive_id = 115)"
" -> Bitmap Index Scan on
tbl_structure_idx3 (cost=0.00..615.59 rows=5979 width=0) (actual
time=126.623..126.623 rows=11420 loops=1)"
" Index Cond: (fk_archive_id = 115)"
" -> Index Scan using tbl_tar_pkey on tbl_tar (cost=0.00..106.86
rows=3421 width=34) (actual time=22.218..251.830 rows=2491 loops=1)"
"Total runtime: 36292.481 ms"
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
