#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ Sort (cost=37038713.42..37166487.90 rows=51109790 width=162) (actual time=.. rows= loops=) Merge Cond: (p.node_id = n.id) Merge Cond: (main.id = pa.portfolio_id) Merge Cond: (main.id = n.portfolio_id) Merge Cond: (main.id = cte_docs.id) Filter: ((ver_from_instant <= '2012-04-27 13:34:10.271'::timestamp without time zone) AND (ver_to_instant > '2012-04-27 13:34:10.271'::timestamp without time zone) AND (corr_from_instant <= '2012-04-27 13:34:10.271'::timestamp without time zone) AND (corr_to_instant > '2012-04-27 13:34:10.271'::timestamp without time zone) AND (visibility <= 25))
2.				CTE cte_docs
3.	0.000	0.000	↓ 0.0	→ Sort (cost=7607942.10..7646077.58 rows=15254190 width=145) (actual time=.. rows= loops=)
4.	0.000	0.000	↓ 0.0	→ Sort (cost=378215.35..383334.32 rows=2047588 width=19) (actual time=.. rows= loops=)
5.	0.000	0.000	↓ 0.0	→ Sort (cost=173285.53..175850.52 rows=1025997 width=16) (actual time=.. rows= loops=)
6.	0.000	0.000	↓ 0.0	→ Sort (cost=151014.53..153579.53 rows=1025997 width=8) (actual time=.. rows= loops=)
7.	0.000	0.000	↓ 0.0	→ Seq Scan on prt_portfolio (cost=0.00..35776.93 rows=1025997 width=16) (actual time=.. rows= loops=)
8.	0.000	0.000	↓ 0.0	→ Seq Scan on prt_portfolio_attribute pa (cost=0.00..37592.88 rows=2047588 width=19) (actual time=.. rows= loops=)
9.	0.000	0.000	↓ 0.0	→ Merge Right Join (cost=7608017.86..9355991.33 rows=51109790 width=162) (actual time=.. rows= loops=)
10.	0.000	0.000	↓ 0.0	→ Merge Left Join (cost=529268.39..1199733.13 rows=15254190 width=145) (actual time=.. rows= loops=)
11.	0.000	0.000	↓ 0.0	→ Merge Left Join (cost=529229.88..626759.01 rows=1971797 width=82) (actual time=.. rows= loops=)
12.	0.000	0.000	↓ 0.0	→ Merge Join (cost=151014.53..210903.79 rows=1025997 width=71) (actual time=.. rows= loops=)
13.	0.000	0.000	↓ 0.0	→ Materialize (cost=7607942.10..7684213.05 rows=15254190 width=145) (actual time=.. rows= loops=)
14.	0.000	0.000	↓ 0.0	→ Materialize (cost=378215.35..388453.29 rows=2047588 width=19) (actual time=.. rows= loops=)
15.	0.000	0.000	↓ 0.0	→ Materialize (cost=151014.53..156144.52 rows=1025997 width=8) (actual time=.. rows= loops=)
16.	0.000	0.000	↓ 0.0	→ Materialize (cost=0.00..359075.00 rows=7937306 width=71) (actual time=.. rows= loops=)
17.	0.000	0.000	↓ 0.0	→ Index Scan using prt_portfolio_pkey on prt_portfolio main (cost=0.00..39369.32 rows=1025997 width=71) (actual time=.. rows= loops=)
18.	0.000	0.000	↓ 0.0	→ Index Scan using ix_prt_position_node_id on prt_position p (cost=0.00..902552.31 rows=26594270 width=25) (actual time=.. rows= loops=)
19.	0.000	0.000	↓ 0.0	→ Index Scan using ix_prt_node_portfolio_id on prt_node n (cost=0.00..339231.73 rows=7937306 width=71) (actual time=.. rows= loops=)
20.	0.000	0.000	↓ 0.0	→ CTE Scan on cte_docs (cost=0.00..20519.94 rows=1025997 width=8) (actual time=.. rows= loops=)

          Sort Key: prt_portfolio.oid, prt_portfolio.id
                                            Sort Key: pa.portfolio_id
                    Sort Key: n.id
  Sort Key: main.oid, main.id, n.tree_left, p.key_scheme, p.key_value
                                                  Sort Key: cte_docs.id
Sort  (cost=37038713.42..37166487.90 rows=51109790 width=162)
        Merge Cond: (p.node_id = n.id)
                                Merge Cond: (main.id = pa.portfolio_id)
                          Merge Cond: (main.id = n.portfolio_id)
                                      Merge Cond: (main.id = cte_docs.id)
                Filter: ((ver_from_instant <= '2012-04-27 13:34:10.271'::timestamp without time zone) AND (ver_to_instant > '2012-04-27 13:34:10.271'::timestamp without time zone) AND (corr_from_instant <= '2012-04-27 13:34:10.271'::timestamp without time zone) AND (corr_to_instant > '2012-04-27 13:34:10.271'::timestamp without time zone) AND (visibility <= 25))
  CTE cte_docs
              ->  Sort  (cost=7607942.10..7646077.58 rows=15254190 width=145)
                                      ->  Sort  (cost=378215.35..383334.32 rows=2047588 width=19)
    ->  Sort  (cost=173285.53..175850.52 rows=1025997 width=16)
                                            ->  Sort  (cost=151014.53..153579.53 rows=1025997 width=8)
          ->  Seq Scan on prt_portfolio  (cost=0.00..35776.93 rows=1025997 width=16)
                                            ->  Seq Scan on prt_portfolio_attribute pa  (cost=0.00..37592.88 rows=2047588 width=19)
  ->  Merge Right Join  (cost=7608017.86..9355991.33 rows=51109790 width=162)
                    ->  Merge Left Join  (cost=529268.39..1199733.13 rows=15254190 width=145)
                          ->  Merge Left Join  (cost=529229.88..626759.01 rows=1971797 width=82)
                                ->  Merge Join  (cost=151014.53..210903.79 rows=1025997 width=71)
        ->  Materialize  (cost=7607942.10..7684213.05 rows=15254190 width=145)
                                ->  Materialize  (cost=378215.35..388453.29 rows=2047588 width=19)
                                      ->  Materialize  (cost=151014.53..156144.52 rows=1025997 width=8)
                          ->  Materialize  (cost=0.00..359075.00 rows=7937306 width=71)
                                      ->  Index Scan using prt_portfolio_pkey on prt_portfolio main  (cost=0.00..39369.32 rows=1025997 width=71)
        ->  Index Scan using ix_prt_position_node_id on prt_position p  (cost=0.00..902552.31 rows=26594270 width=25)
                                ->  Index Scan using ix_prt_node_portfolio_id on prt_node n  (cost=0.00..339231.73 rows=7937306 width=71)
                                                  ->  CTE Scan on cte_docs  (cost=0.00..20519.94 rows=1025997 width=8)

Per node type stats

node type	count	sum of times	% of query
CTE Scan	1	0.000 ms	0.0 %
Index Scan	3	0.000 ms	0.0 %
Materialize	4	0.000 ms	0.0 %
Merge Join	1	0.000 ms	0.0 %
Merge Left Join	2	0.000 ms	0.0 %
Merge Right Join	1	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
Sort	5	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
prt_node	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
prt_portfolio	2	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
prt_portfolio_attribute	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
prt_position	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: mPj

Colorize:

Columns visibility:

Per node type stats

Per table stats