#	exclusive	inclusive	rows x	rows	loops	node
1.	0.669	2,414.977	↓ 3.7	6,370	1	→ Limit (cost=18,877,454.76..18,877,459.10 rows=1,735 width=409) (actual time=2,411.373..2,414.977 rows=6,370 loops=1)
2.	30.849	2,414.308	↓ 3.7	6,370	1	→ Sort (cost=18,877,454.76..18,877,459.10 rows=1,735 width=409) (actual time=2,411.372..2,414.308 rows=6,370 loops=1) Sort Key: fi.periodenddate DESC Sort Method: external merge Disk: 3216kB
3.	4.218	2,383.459	↓ 3.7	6,370	1	→ Nested Loop (cost=2.57..18,877,361.41 rows=1,735 width=409) (actual time=1,412.259..2,383.459 rows=6,370 loops=1)
4.	2.637	2,360.131	↓ 3.7	6,370	1	→ Nested Loop (cost=2.28..18,863,781.69 rows=1,735 width=189) (actual time=1,412.244..2,360.131 rows=6,370 loops=1)
5.	10.230	1,416.144	↓ 2.3	281	1	→ Nested Loop (cost=1.57..2,000.44 rows=120 width=162) (actual time=1,404.033..1,416.144 rows=281 loops=1) Join Filter: (fc.currencyid = c.currencyid) Rows Removed by Join Filter: 64630
6.	0.048	0.048	↑ 1.0	231	1	→ Seq Scan on ciqcurrency c (cost=0.00..4.31 rows=231 width=30) (actual time=0.004..0.048 rows=231 loops=1)
7.	4.667	1,405.866	↓ 2.3	281	231	→ Materialize (cost=1.57..1,580.63 rows=120 width=132) (actual time=4.457..6.086 rows=281 loops=231)
8.	0.206	1,401.199	↓ 2.3	281	1	→ Nested Loop (cost=1.57..1,580.03 rows=120 width=132) (actual time=1,029.640..1,401.199 rows=281 loops=1)
9.	0.342	1,191.043	↓ 1.9	17	1	→ Nested Loop (cost=1.00..863.93 rows=9 width=112) (actual time=1,023.056..1,191.043 rows=17 loops=1)
10.	147.373	147.373	↓ 2.0	104	1	→ Index Scan using ix_ciqfinperiod_companyid_periodtypeid on ciqfinperiod fp (cost=0.43..181.37 rows=51 width=34) (actual time=3.775..147.373 rows=104 loops=1) Index Cond: ((companyid = 105612) AND (periodtypeid = 2))
11.	1,043.328	1,043.328	↓ 0.0	0	104	→ Index Scan using ix_ciqfininstance on ciqfininstance fi (cost=0.56..13.37 rows=1 width=78) (actual time=9.928..10.032 rows=0 loops=104) Index Cond: (financialperiodid = fp.financialperiodid) Filter: ((filingdate >= '2016-01-23 00:00:00'::timestamp without time zone) AND (latestforfinancialperiodflag = 1)) Rows Removed by Filter: 4
12.	209.950	209.950	↑ 17.6	17	17	→ Index Scan using ix_ciqfincollection on ciqfincollection fc (cost=0.57..76.57 rows=300 width=20) (actual time=6.137..12.350 rows=17 loops=17) Index Cond: (financialinstanceid = fi.financialinstanceid)
13.	941.350	941.350	↑ 2,238.8	23	281	→ Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata cd (cost=0.71..156,666.59 rows=51,492 width=27) (actual time=1.853..3.350 rows=23 loops=281) Index Cond: (financialcollectionid = fc.financialcollectionid)
14.	19.110	19.110	↑ 1.0	1	6,370	→ Index Scan using pk_ciqdataitem on ciqdataitem di (cost=0.29..7.82 rows=1 width=220) (actual time=0.003..0.003 rows=1 loops=6,370) Index Cond: (dataitemid = cd.dataitemid)

Limit  (cost=18877454.76..18877459.10 rows=1735 width=409) (actual time=2411.373..2414.977 rows=6370 loops=1)
  ->  Sort  (cost=18877454.76..18877459.10 rows=1735 width=409) (actual time=2411.372..2414.308 rows=6370 loops=1)
        Sort Key: fi.periodenddate DESC
        Sort Method: external merge  Disk: 3216kB
        ->  Nested Loop  (cost=2.57..18877361.41 rows=1735 width=409) (actual time=1412.259..2383.459 rows=6370 loops=1)
              ->  Nested Loop  (cost=2.28..18863781.69 rows=1735 width=189) (actual time=1412.244..2360.131 rows=6370 loops=1)
                    ->  Nested Loop  (cost=1.57..2000.44 rows=120 width=162) (actual time=1404.033..1416.144 rows=281 loops=1)
                          Join Filter: (fc.currencyid = c.currencyid)
                          Rows Removed by Join Filter: 64630
                          ->  Seq Scan on ciqcurrency c  (cost=0.00..4.31 rows=231 width=30) (actual time=0.004..0.048 rows=231 loops=1)
                          ->  Materialize  (cost=1.57..1580.63 rows=120 width=132) (actual time=4.457..6.086 rows=281 loops=231)
                                ->  Nested Loop  (cost=1.57..1580.03 rows=120 width=132) (actual time=1029.640..1401.199 rows=281 loops=1)
                                      ->  Nested Loop  (cost=1.00..863.93 rows=9 width=112) (actual time=1023.056..1191.043 rows=17 loops=1)
                                            ->  Index Scan using ix_ciqfinperiod_companyid_periodtypeid on ciqfinperiod fp  (cost=0.43..181.37 rows=51 width=34) (actual time=3.775..147.373 rows=104 loops=1)
                                                  Index Cond: ((companyid = 105612) AND (periodtypeid = 2))
                                            ->  Index Scan using ix_ciqfininstance on ciqfininstance fi  (cost=0.56..13.37 rows=1 width=78) (actual time=9.928..10.032 rows=0 loops=104)
                                                  Index Cond: (financialperiodid = fp.financialperiodid)
                                                  Filter: ((filingdate >= '2016-01-23 00:00:00'::timestamp without time zone) AND (latestforfinancialperiodflag = 1))
                                                  Rows Removed by Filter: 4
                                      ->  Index Scan using ix_ciqfincollection on ciqfincollection fc  (cost=0.57..76.57 rows=300 width=20) (actual time=6.137..12.350 rows=17 loops=17)
                                            Index Cond: (financialinstanceid = fi.financialinstanceid)
                    ->  Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata cd  (cost=0.71..156666.59 rows=51492 width=27) (actual time=1.853..3.350 rows=23 loops=281)
                          Index Cond: (financialcollectionid = fc.financialcollectionid)
              ->  Index Scan using pk_ciqdataitem on ciqdataitem di  (cost=0.29..7.82 rows=1 width=220) (actual time=0.003..0.003 rows=1 loops=6370)
                    Index Cond: (dataitemid = cd.dataitemid)
Planning time: 2.131 ms
Execution time: 2416.404 ms

Per node type stats

node type	count	sum of times	% of query
Index Scan	5	2,361.111 ms	97.8 %
Limit	1	0.669 ms	0.0 %
Materialize	1	4.667 ms	0.2 %
Nested Loop	5	17.633 ms	0.7 %
Seq Scan	1	0.048 ms	0.0 %
Sort	1	30.849 ms	1.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ciqcurrency	1	0.048 ms	0.0 %
Seq Scan	1	0.048 ms	100.0 %
ciqdataitem	1	19.110 ms	0.8 %
Index Scan	1	19.110 ms	100.0 %
ciqfincollection	1	209.950 ms	8.7 %
Index Scan	1	209.950 ms	100.0 %
ciqfincollectiondata	1	941.350 ms	39.0 %
Index Scan	1	941.350 ms	100.0 %
ciqfininstance	1	1,043.328 ms	43.2 %
Index Scan	1	1,043.328 ms	100.0 %
ciqfinperiod	1	147.373 ms	6.1 %
Index Scan	1	147.373 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ezyj

Color mode:

Visible columns:

Per node type stats

Per table stats