explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ATvN : "Gather (cost=31700.70..147080.67 rows=535790 width=646) (actual time=5591.476..34496.330 rows=535892 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Hash Left Join (cost=30700.70..92501.67 rows=223246 width=646) (actual time=4937.030..32505.046 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi_ni_vue_proj_infr = sm001000_pi2_projette_3ext.sm001000_pi2_projet_infrastructureid)" " -> Hash Left Join (cost=23940.43..82922.92 rows=223246 width=612) (actual time=4834.521..32333.826 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi2_dossier_transfertid = sm001000_pi2_projette_2ext.sm001000_pi2_dossier_transfertid)" " -> Hash Left Join (cost=23657.19..79821.22 rows=223246 width=605) (actual time=4832.380..32265.976 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi2_projet_infrastructureid = sm001000_pi2_projette_1ext.sm001000_pi2_projet_infrastructureid)" " -> Hash Left Join (cost=16896.92..70242.51 rows=223246 width=571) (actual time=4695.226..31997.493 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi9_motif_penaliteid = sm001000_pi2_est_associe_a_3ext.sm001000_pi9_motif_penaliteid)" " -> Hash Left Join (cost=16895.83..67423.07 rows=223246 width=551) (actual time=4695.206..31933.669 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi9_type_programmeid = sm001000_pi2_concerne_8ext.sm001000_pi9_type_programmeid)" " -> Hash Left Join (cost=16894.78..65594.07 rows=223246 width=541) (actual time=4695.167..31848.007 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi2_reclamation_depenseid = sm001000_pi2_concerne_34ext.sm001000_pi2_reclamation_depenseid)" " -> Hash Left Join (cost=15378.69..61259.50 rows=223246 width=513) (actual time=4678.428..31765.725 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi_ni_vue_proj_infr = sm001000_pi2_projette_3.sm001000_pi2_projet_infrastructureid)" " -> Hash Left Join (cost=8618.42..51680.75 rows=223246 width=509) (actual time=4649.271..31674.561 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi2_dossier_transfertid = sm001000_pi2_projette_2.sm001000_pi2_dossier_transfertid)" " -> Hash Left Join (cost=8335.18..48579.04 rows=223246 width=505) (actual time=4393.647..31351.765 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi2_projet_infrastructureid = sm001000_pi2_projette_1.sm001000_pi2_projet_infrastructureid)" " -> Hash Left Join (cost=1574.91..39000.33 rows=223246 width=501) (actual time=3444.694..30275.575 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi9_motif_penaliteid = sm001000_pi2_est_associe_a_3.sm001000_pi9_motif_penaliteid)" " -> Hash Left Join (cost=1573.82..36180.90 rows=223246 width=497) (actual time=3444.668..30216.769 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi9_type_programmeid = sm001000_pi2_concerne_8.sm001000_pi9_type_programmeid)" " -> Hash Left Join (cost=1572.78..34351.89 rows=223246 width=493) (actual time=3444.626..30141.999 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.sm001000_pi2_reclamation_depenseid = sm001000_pi2_concerne_34.sm001000_pi2_reclamation_depenseid)" " -> Hash Left Join (cost=56.69..30017.32 rows=223246 width=489) (actual time=76.298..26712.922 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.organizationid = organization_sm001000_pi2_repartition_annuel_envelopp.organizationid)" " -> Hash Left Join (cost=55.66..29177.01 rows=223246 width=478) (actual time=76.270..26638.783 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.modifiedby = lk_sm001000_pi2_repartition_annuel_envelopp_modifiedby.systemuserid)" " -> Hash Left Join (cost=27.83..26335.24 rows=223246 width=456) (actual time=76.068..26564.177 rows=178631 loops=3)" " Hash Cond: (sm001000_pi2_repartition_annuel_enveloppbase.createdby = lk_sm001000_pi2_repartition_annuel_envelopp_createdby.systemuserid)" " -> Parallel Seq Scan on sm001000_pi2_repartition_annuel_enveloppbase (cost=0.00..23493.46 rows=223246 width=434) (actual time=67.316..26416.530 rows=178631 loops=3)" " -> Hash (cost=22.37..22.37 rows=437 width=38) (actual time=8.588..8.588 rows=437 loops=3)" " Buckets: 1024 Batches: 1 Memory Usage: 38kB" " -> Seq Scan on systemuserbase lk_sm001000_pi2_repartition_annuel_envelopp_createdby (cost=0.00..22.37 rows=437 width=38) (actual time=0.504..8.478 rows=437 loops=3)" " -> Hash (cost=22.37..22.37 rows=437 width=38) (actual time=0.193..0.193 rows=437 loops=3)" " Buckets: 1024 Batches: 1 Memory Usage: 38kB" " -> Seq Scan on systemuserbase lk_sm001000_pi2_repartition_annuel_envelopp_modifiedby (cost=0.00..22.37 rows=437 width=38) (actual time=0.003..0.094 rows=437 loops=3)" " -> Hash (cost=1.01..1.01 rows=1 width=27) (actual time=0.017..0.017 rows=1 loops=3)" " Buckets: 1024 Batches: 1 Memory Usage: 9kB" " -> Seq Scan on organizationbase organization_sm001000_pi2_repartition_annuel_envelopp (cost=0.00..1.01 rows=1 width=27) (actual time=0.011..0.011 rows=1 loops=3)" " -> Hash (cost=1331.04..1331.04 rows=14804 width=20) (actual time=3368.271..3368.271 rows=14804 loops=3)" " Buckets: 16384 Batches: 1 Memory Usage: 880kB" " -> Seq Scan on sm001000_pi2_reclamation_depensebase sm001000_pi2_concerne_34 (cost=0.00..1331.04 rows=14804 width=20) (actual time=123.589..3362.904 rows=14804 loops=3)" " -> Hash (cost=1.02..1.02 rows=2 width=20) (actual time=0.021..0.021 rows=2 loops=3)" " Buckets: 1024 Batches: 1 Memory Usage: 9kB" " -> Seq Scan on sm001000_pi9_type_programmebase sm001000_pi2_concerne_8 (cost=0.00..1.02 rows=2 width=20) (actual time=0.017..0.018 rows=2 loops=3)" " -> Hash (cost=1.04..1.04 rows=4 width=20) (actual time=0.014..0.014 rows=4 loops=3)" " Buckets: 1024 Batches: 1 Memory Usage: 9kB" " -> Seq Scan on sm001000_pi9_motif_penalitebase sm001000_pi2_est_associe_a_3 (cost=0.00..1.04 rows=4 width=20) (actual time=0.010..0.011 rows=4 loops=3)" " -> Hash (cost=6255.12..6255.12 rows=40412 width=20) (actual time=948.720..948.720 rows=40431 loops=3)" " Buckets: 65536 Batches: 1 Memory Usage: 2566kB" " -> Seq Scan on sm001000_pi2_projet_infrastructurebase sm001000_pi2_projette_1 (cost=0.00..6255.12 rows=40412 width=20) (actual time=0.011..929.014 rows=40431 loops=3)" " -> Hash (cost=255.33..255.33 rows=2233 width=20) (actual time=255.587..255.587 rows=2233 loops=3)" " Buckets: 4096 Batches: 1 Memory Usage: 146kB" " -> Seq Scan on sm001000_pi2_dossier_transfertbase sm001000_pi2_projette_2 (cost=0.00..255.33 rows=2233 width=20) (actual time=0.945..254.736 rows=2233 loops=3)" " -> Hash (cost=6255.12..6255.12 rows=40412 width=20) (actual time=28.910..28.910 rows=40431 loops=3)" " Buckets: 65536 Batches: 1 Memory Usage: 2566kB" " -> Seq Scan on sm001000_pi2_projet_infrastructurebase sm001000_pi2_projette_3 (cost=0.00..6255.12 rows=40412 width=20) (actual time=0.008..20.107 rows=40431 loops=3)" " -> Hash (cost=1331.04..1331.04 rows=14804 width=44) (actual time=16.671..16.671 rows=14804 loops=3)" " Buckets: 16384 Batches: 1 Memory Usage: 1235kB" " -> Seq Scan on sm001000_pi2_reclamation_depensebase sm001000_pi2_concerne_34ext (cost=0.00..1331.04 rows=14804 width=44) (actual time=0.007..13.195 rows=14804 loops=3)" " -> Hash (cost=1.02..1.02 rows=2 width=26) (actual time=0.023..0.023 rows=2 loops=3)" " Buckets: 1024 Batches: 1 Memory Usage: 9kB" " -> Seq Scan on sm001000_pi9_type_programmebase sm001000_pi2_concerne_8ext (cost=0.00..1.02 rows=2 width=26) (actual time=0.005..0.006 rows=2 loops=3)" " -> Hash (cost=1.04..1.04 rows=4 width=36) (actual time=0.010..0.010 rows=4 loops=3)" " Buckets: 1024 Batches: 1 Memory Usage: 9kB" " -> Seq Scan on sm001000_pi9_motif_penalitebase sm001000_pi2_est_associe_a_3ext (cost=0.00..1.04 rows=4 width=36) (actual time=0.004..0.005 rows=4 loops=3)" " -> Hash (cost=6255.12..6255.12 rows=40412 width=50) (actual time=135.672..135.672 rows=40431 loops=3)" " Buckets: 65536 Batches: 1 Memory Usage: 3771kB" " -> Seq Scan on sm001000_pi2_projet_infrastructurebase sm001000_pi2_projette_1ext (cost=0.00..6255.12 rows=40412 width=50) (actual time=0.016..119.856 rows=40431 loops=3)" " -> Hash (cost=255.33..255.33 rows=2233 width=23) (actual time=2.116..2.116 rows=2233 loops=3)" " Buckets: 4096 Batches: 1 Memory Usage: 154kB" " -> Seq Scan on sm001000_pi2_dossier_transfertbase sm001000_pi2_projette_2ext (cost=0.00..255.33 rows=2233 width=23) (actual time=0.008..1.618 rows=2233 loops=3)" " -> Hash (cost=6255.12..6255.12 rows=40412 width=50) (actual time=102.274..102.274 rows=40431 loops=3)" " Buckets: 65536 Batches: 1 Memory Usage: 3771kB" " -> Seq Scan on sm001000_pi2_projet_infrastructurebase sm001000_pi2_projette_3ext (cost=0.00..6255.12 rows=40412 width=50) (actual time=0.007..87.635 rows=40431 loops=3)" "Planning time: 808.685 ms" "Execution time: 34532.291 ms"

Settings
# exclusive inclusive rows x rows loops node
1. 0.158 55.012 ↑ 11.4 71 1

Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)

  • Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
  • Sort Method: quicksort Memory: 43kB
2. 1.378 54.854 ↑ 11.4 71 1

Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)

  • Hash Cond: (p.pronamespace = n.oid)
3. 53.465 53.465 ↓ 3.0 2,402 1

Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2,402 loops=1)

  • Filter: pg_function_is_visible(oid)
4. 0.004 0.011 ↑ 1.0 4 1

Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
5. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)

  • Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))