6 aug 2007 kl. 15:07 skrev Gregory Stark:
"Henrik Zagerholm" <henke@xxxxxx> writes:
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.
Well the way Postgres disables a plan node type is by giving it a
cost of
100,000,000. What other way did you expect it to be able to scan
tbl_filetype_suffix anyways? What indexes do you have on
tbl_filetype_suffix?
Ahh, my bad. It is a very small table but I have an unique index.
CREATE UNIQUE INDEX tbl_filetype_suffix_idx ON tbl_filetype_suffix
USING btree (filetype_suffix);
And any chance you could resend this stuff without the word-wrapping?
It's pretty hard to read like this:
Resending and hopefully the line breaks are gone. I couldn't find any
in my sent mail. If this doesn't work I'll paste it on pastie.
The weird thing is that the seq scan on tbl_file_structure is soooo
slow but when I force an index scan it is very fast.
" -> 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)"
Cheers,
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
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that
your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
