Hi,
How, according to EXPLAIN ANALYZE, the Sort node could return more rows
as output (rows=767662), than it had for input from its child node
(rows=135627)?
-> Merge Join .... (actual time=1977.388..333626.072 rows=725757 loops=1)
-> Index Scan using .... (actual time=0.013..312144.441
rows=49176765 loops=1)
-> Sort .... (actual time=1977.363..2274.092 rows=767662 loops=1)
-> Hash Left Join .... (actual time=97.123..1887.956
rows=135627 loops=1)
(full plan attached, PostgreSQL 9.3)
Regards,
Vitaliy
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=6051876.28..6062663.17 rows=359563 width=65) (actual
time=604213.422..614449.378 rows=86325 loops=1)
-> Sort (cost=6051876.28..6052775.19 rows=359563 width=65) (actual
time=604213.378..609416.622 rows=5563369 loops=1)
Sort Key: cmdb_program_daily_usage.used_from,
cmdb_program_daily_usage."user", cmdb_software_product_version.friendly_name,
cmdb_software_product_version.id
Sort Method: external merge Disk: 445928kB
-> Hash Join (cost=197462.19..6018696.01 rows=359563 width=65)
(actual time=4136.668..533171.915 rows=5563369 loops=1)
Hash Cond: (cmdb_ci.id = cmdb_ci_computer.id)
-> Nested Loop (cost=194838.34..6011562.23 rows=359563
width=73) (actual time=3909.509..527042.088 rows=5563369 loops=1)
-> Nested Loop (cost=194837.91..5794445.19 rows=459672
width=65) (actual time=3909.321..467581.211 rows=5828546 loops=1)
-> Hash Left Join (cost=194837.21..3206507.24
rows=343355 width=37) (actual time=3909.179..337915.666 rows=725757 loops=1)
Hash Cond: (cmdb_program_instance.program =
cmdb_program.id)
Filter: ((NOT
cmdb_program.ssi_exclude_from_usage) OR (cmdb_program.ssi_exclude_from_usage IS
NULL))
-> Merge Join (cost=120277.29..3123792.65
rows=343355 width=45) (actual time=1977.388..333626.072 rows=725757 loops=1)
Merge Cond: (cmdb_program_instance.spkg
= cmdb_ci_spkg.id)
-> Index Scan using
idx_fk_2005379175958 on cmdb_program_instance (cost=0.57..5178525.28
rows=88519832 width=24) (actual time=0.013..312144.441 rows=49176765 loops=1)
-> Sort (cost=120276.66..120493.07
rows=86563 width=37) (actual time=1977.363..2274.092 rows=767662 loops=1)
Sort Key: cmdb_ci_spkg.id
Sort Method: quicksort Memory:
16740kB
-> Hash Left Join
(cost=2024.59..113177.86 rows=86563 width=37) (actual time=97.123..1887.956
rows=135627 loops=1)
Hash Cond:
(cmdb_ci_spkg.software = cmdb_software_product_version.id)
-> Bitmap Heap Scan on
cmdb_ci_spkg (cost=2021.27..111984.31 rows=86563 width=16) (actual
time=96.993..1780.596 rows=135627 loops=1)
Recheck Cond:
((software IS NOT NULL) AND (software = 2))
Filter: ((NOT os) OR
(os IS NULL))
-> Bitmap Index Scan
on idx_fk_1998473864224 (cost=0.00..1999.63 rows=86957 width=0) (actual
time=65.665..65.665 rows=136536 loops=1)
Index Cond:
((software IS NOT NULL) AND (software = 2))
-> Hash (cost=3.30..3.30
rows=1 width=29) (actual time=0.088..0.088 rows=1 loops=1)
Buckets: 1024
Batches: 1 Memory Usage: 1kB
-> Index Scan using
cmdb_software_product_version_pkey on cmdb_software_product_version
(cost=0.29..3.30 rows=1 width=29) (actual time=0.068..0.083 rows=1 loops=1)
Index Cond: (id
= 2)
-> Hash (cost=59002.74..59002.74
rows=1244574 width=9) (actual time=1931.213..1931.213 rows=1230216 loops=1)
Buckets: 131072 Batches: 1 Memory
Usage: 49257kB
-> Seq Scan on cmdb_program
(cost=0.00..59002.74 rows=1244574 width=9) (actual time=0.026..1496.853
rows=1230216 loops=1)
-> Index Scan using idx_2011674031584 on
cmdb_program_daily_usage (cost=0.70..6.67 rows=87 width=44) (actual
time=0.047..0.172 rows=8 loops=725757)
Index Cond: ((program_instance =
cmdb_program_instance.id) AND (usage_date >= '2017-07-13'::date) AND
(usage_date <= '2018-01-09'::date))
-> Index Scan using cmdb_ci_pkey on cmdb_ci
(cost=0.43..0.46 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=5828546)
Index Cond: (id = cmdb_program_daily_usage.used_from)
Filter: operational
-> Hash (cost=1815.19..1815.19 rows=64693 width=8) (actual
time=227.122..227.122 rows=64705 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 2528kB
-> Index Only Scan using cmdb_ci_computer_pkey on
cmdb_ci_computer (cost=0.41..1815.19 rows=64693 width=8) (actual
time=0.037..205.223 rows=64705 loops=1)
Heap Fetches: 22752
Total runtime: 614596.450 ms
(41 rows)
