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)
