#	exclusive	inclusive	rows x	rows	loops	node
1.	14.178	1,618.230	↑ 198.4	64	1	→ HashAggregate (cost=4,101,587.13..4,101,841.13 rows=12,700 width=116) (actual time=1,618.153..1,618.230 rows=64 loops=1) Group Key: latest_ranking_params.market_id, latest_ranking_params.market, (latest_ranking_params.coordinate)::text
2.						CTE latest_ranking_params
3.	12.324	1,547.861	↑ 6.4	19,904	1	→ Nested Loop Left Join (cost=792,207.51..4,096,507.33 rows=126,995 width=144) (actual time=954.588..1,547.861 rows=19,904 loops=1)
4.	0.000	1,356.401	↑ 6.4	19,904	1	→ Nested Loop Left Join (cost=792,207.09..2,998,000.58 rows=126,995 width=110) (actual time=954.559..1,356.401 rows=19,904 loops=1)
5.	128.096	1,217.090	↑ 6.4	19,904	1	→ Hash Left Join (cost=792,206.53..1,905,526.10 rows=126,995 width=110) (actual time=954.536..1,217.090 rows=19,904 loops=1) Hash Cond: ((d_1.domain_id = r.domain_id) AND (dk.keyword_id = r.keyword_id) AND (dm.market_id = r.market_id) AND (multisample_rankings.date = r.date) AND (d.name = r.domain))
6.	7.499	137.071	↑ 6.4	19,904	1	→ Nested Loop Left Join (cost=161.15..1,097,869.91 rows=126,995 width=100) (actual time=2.045..137.071 rows=19,904 loops=1)
7.	3.686	10.148	↑ 6.4	19,904	1	→ Nested Loop (cost=160.59..5,395.43 rows=126,995 width=96) (actual time=2.031..10.148 rows=19,904 loops=1)
8.	0.083	1.278	↑ 5.0	64	1	→ Nested Loop (cost=0.87..3,252.72 rows=320 width=52) (actual time=0.030..1.278 rows=64 loops=1) Join Filter: (dm.market_id = m.market_id)
9.	0.174	1.131	↑ 5.0	64	1	→ Nested Loop (cost=0.58..2,446.81 rows=320 width=12) (actual time=0.027..1.131 rows=64 loops=1)
10.	0.191	0.191	↓ 1.1	383	1	→ Index Scan using domains_markets_pkey on domains_markets dm (cost=0.29..460.72 rows=358 width=8) (actual time=0.019..0.191 rows=383 loops=1) Index Cond: (domain_id = 725) Filter: (NOT deleted)
11.	0.766	0.766	↓ 0.0	0	383	→ Index Scan using markets_pkey on markets m_1 (cost=0.29..5.55 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=383) Index Cond: (market_id = dm.market_id) Filter: (parent_id IS NULL) Rows Removed by Filter: 1
12.	0.064	0.064	↑ 1.0	1	64	→ Index Scan using markets_pkey on markets m (cost=0.29..2.51 rows=1 width=48) (actual time=0.001..0.001 rows=1 loops=64) Index Cond: (market_id = m_1.market_id)
13.	1.467	5.184	↑ 1.3	311	64	→ Materialize (cost=159.72..555.70 rows=397 width=48) (actual time=0.031..0.081 rows=311 loops=64)
14.	0.058	3.717	↑ 1.3	311	1	→ Nested Loop (cost=159.72..553.71 rows=397 width=48) (actual time=1.999..3.717 rows=311 loops=1)
15.	0.001	0.016	↑ 1.0	1	1	→ Nested Loop (cost=0.56..16.60 rows=1 width=21) (actual time=0.015..0.016 rows=1 loops=1)
16.	0.012	0.012	↑ 1.0	1	1	→ Index Scan using domains_pkey on domains d (cost=0.28..8.29 rows=1 width=21) (actual time=0.012..0.012 rows=1 loops=1) Index Cond: (domain_id = 725)
17.	0.003	0.003	↑ 1.0	1	1	→ Index Only Scan using domains_pkey on domains d_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1) Index Cond: (domain_id = 725) Heap Fetches: 1
18.	1.570	3.643	↑ 1.3	311	1	→ Hash Join (cost=159.17..533.14 rows=397 width=31) (actual time=1.984..3.643 rows=311 loops=1) Hash Cond: (k.keyword_id = dk.keyword_id)
19.	1.522	1.522	↑ 1.0	17,106	1	→ Seq Scan on keywords k (cost=0.00..329.06 rows=17,106 width=27) (actual time=0.005..1.522 rows=17,106 loops=1)
20.	0.042	0.551	↑ 1.3	311	1	→ Hash (cost=154.21..154.21 rows=397 width=8) (actual time=0.551..0.551 rows=311 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 21kB
21.	0.480	0.509	↑ 1.3	311	1	→ Bitmap Heap Scan on domains_keywords dk (cost=11.69..154.21 rows=397 width=8) (actual time=0.043..0.509 rows=311 loops=1) Recheck Cond: (domain_id = 725) Filter: (NOT deleted) Rows Removed by Filter: 130 Heap Blocks: exact=114
22.	0.029	0.029	↑ 1.0	441	1	→ Bitmap Index Scan on domains_keywords_pkey (cost=0.00..11.59 rows=441 width=0) (actual time=0.029..0.029 rows=441 loops=1) Index Cond: (domain_id = 725)
23.	0.000	119.424	↑ 1.0	1	19,904	→ Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=19,904)
24.	119.424	119.424	↑ 1.0	1	19,904	→ Index Only Scan Backward using multisample_rankings_pkey on multisample_rankings (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=19,904) Index Cond: ((domain_id = d_1.domain_id) AND (keyword_id = dk.keyword_id) AND (market_id = dm.market_id)) Heap Fetches: 13030
25.	362.589	951.923	↓ 1.0	1,135,527	1	→ Hash (cost=756,538.15..756,538.15 rows=1,134,810 width=41) (actual time=951.923..951.923 rows=1,135,527 loops=1) Buckets: 65536 Batches: 32 Memory Usage: 3144kB
26.	589.334	589.334	↓ 1.0	1,135,527	1	→ Index Scan using idx_multisample_rankings_domain_id on multisample_rankings r (cost=0.43..756,538.15 rows=1,134,810 width=41) (actual time=0.016..589.334 rows=1,135,527 loops=1) Index Cond: (domain_id = 725)
27.	19.904	139.328	↑ 1.0	1	19,904	→ Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=19,904)
28.	119.424	119.424	↑ 1.0	1	19,904	→ Index Scan using multisample_rankings_domain_id_market_id_keyword_id_domain__idx on multisample_rankings r_1 (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=19,904) Index Cond: ((d_1.domain_id = domain_id) AND (dm.market_id = market_id) AND (dk.keyword_id = keyword_id))
29.	19.904	179.136	↑ 1.0	1	19,904	→ Limit (cost=0.42..8.63 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=19,904)
30.	159.232	159.232	↑ 1.0	1	19,904	→ Index Scan Backward using adwords_keywords_name_created_at_market_id_idx on adwords_keywords ak (cost=0.42..8.63 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=19,904) Index Cond: ((k.name = name) AND (dm.market_id = market_id))
31.	1,604.052	1,604.052	↑ 6.4	19,904	1	→ CTE Scan on latest_ranking_params (cost=0.00..3,174.88 rows=126,995 width=108) (actual time=954.597..1,604.052 rows=19,904 loops=1)

Planning time	:	3.452 ms
Execution time	:	1,618.781 ms

HashAggregate  (cost=4101587.13..4101841.13 rows=12700 width=116) (actual time=1618.153..1618.230 rows=64 loops=1)
  Group Key: latest_ranking_params.market_id, latest_ranking_params.market, (latest_ranking_params.coordinate)::text
  CTE latest_ranking_params
    ->  Nested Loop Left Join  (cost=792207.51..4096507.33 rows=126995 width=144) (actual time=954.588..1547.861 rows=19904 loops=1)
          ->  Nested Loop Left Join  (cost=792207.09..2998000.58 rows=126995 width=110) (actual time=954.559..1356.401 rows=19904 loops=1)
                ->  Hash Left Join  (cost=792206.53..1905526.10 rows=126995 width=110) (actual time=954.536..1217.090 rows=19904 loops=1)
                      Hash Cond: ((d_1.domain_id = r.domain_id) AND (dk.keyword_id = r.keyword_id) AND (dm.market_id = r.market_id) AND (multisample_rankings.date = r.date) AND (d.name = r.domain))
                      ->  Nested Loop Left Join  (cost=161.15..1097869.91 rows=126995 width=100) (actual time=2.045..137.071 rows=19904 loops=1)
                            ->  Nested Loop  (cost=160.59..5395.43 rows=126995 width=96) (actual time=2.031..10.148 rows=19904 loops=1)
                                  ->  Nested Loop  (cost=0.87..3252.72 rows=320 width=52) (actual time=0.030..1.278 rows=64 loops=1)
                                        Join Filter: (dm.market_id = m.market_id)
                                        ->  Nested Loop  (cost=0.58..2446.81 rows=320 width=12) (actual time=0.027..1.131 rows=64 loops=1)
                                              ->  Index Scan using domains_markets_pkey on domains_markets dm  (cost=0.29..460.72 rows=358 width=8) (actual time=0.019..0.191 rows=383 loops=1)
                                                    Index Cond: (domain_id = 725)
                                                    Filter: (NOT deleted)
                                              ->  Index Scan using markets_pkey on markets m_1  (cost=0.29..5.55 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=383)
                                                    Index Cond: (market_id = dm.market_id)
                                                    Filter: (parent_id IS NULL)
                                                    Rows Removed by Filter: 1
                                        ->  Index Scan using markets_pkey on markets m  (cost=0.29..2.51 rows=1 width=48) (actual time=0.001..0.001 rows=1 loops=64)
                                              Index Cond: (market_id = m_1.market_id)
                                  ->  Materialize  (cost=159.72..555.70 rows=397 width=48) (actual time=0.031..0.081 rows=311 loops=64)
                                        ->  Nested Loop  (cost=159.72..553.71 rows=397 width=48) (actual time=1.999..3.717 rows=311 loops=1)
                                              ->  Nested Loop  (cost=0.56..16.60 rows=1 width=21) (actual time=0.015..0.016 rows=1 loops=1)
                                                    ->  Index Scan using domains_pkey on domains d  (cost=0.28..8.29 rows=1 width=21) (actual time=0.012..0.012 rows=1 loops=1)
                                                          Index Cond: (domain_id = 725)
                                                    ->  Index Only Scan using domains_pkey on domains d_1  (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)
                                                          Index Cond: (domain_id = 725)
                                                          Heap Fetches: 1
                                              ->  Hash Join  (cost=159.17..533.14 rows=397 width=31) (actual time=1.984..3.643 rows=311 loops=1)
                                                    Hash Cond: (k.keyword_id = dk.keyword_id)
                                                    ->  Seq Scan on keywords k  (cost=0.00..329.06 rows=17106 width=27) (actual time=0.005..1.522 rows=17106 loops=1)
                                                    ->  Hash  (cost=154.21..154.21 rows=397 width=8) (actual time=0.551..0.551 rows=311 loops=1)
                                                          Buckets: 1024  Batches: 1  Memory Usage: 21kB
                                                          ->  Bitmap Heap Scan on domains_keywords dk  (cost=11.69..154.21 rows=397 width=8) (actual time=0.043..0.509 rows=311 loops=1)
                                                                Recheck Cond: (domain_id = 725)
                                                                Filter: (NOT deleted)
                                                                Rows Removed by Filter: 130
                                                                Heap Blocks: exact=114
                                                                ->  Bitmap Index Scan on domains_keywords_pkey  (cost=0.00..11.59 rows=441 width=0) (actual time=0.029..0.029 rows=441 loops=1)
                                                                      Index Cond: (domain_id = 725)
                            ->  Limit  (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=19904)
                                  ->  Index Only Scan Backward using multisample_rankings_pkey on multisample_rankings  (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=19904)
                                        Index Cond: ((domain_id = d_1.domain_id) AND (keyword_id = dk.keyword_id) AND (market_id = dm.market_id))
                                        Heap Fetches: 13030
                      ->  Hash  (cost=756538.15..756538.15 rows=1134810 width=41) (actual time=951.923..951.923 rows=1135527 loops=1)
                            Buckets: 65536  Batches: 32  Memory Usage: 3144kB
                            ->  Index Scan using idx_multisample_rankings_domain_id on multisample_rankings r  (cost=0.43..756538.15 rows=1134810 width=41) (actual time=0.016..589.334 rows=1135527 loops=1)
                                  Index Cond: (domain_id = 725)
                ->  Limit  (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=19904)
                      ->  Index Scan using multisample_rankings_domain_id_market_id_keyword_id_domain__idx on multisample_rankings r_1  (cost=0.56..8.58 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=19904)
                            Index Cond: ((d_1.domain_id = domain_id) AND (dm.market_id = market_id) AND (dk.keyword_id = keyword_id))
          ->  Limit  (cost=0.42..8.63 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=19904)
                ->  Index Scan Backward using adwords_keywords_name_created_at_market_id_idx on adwords_keywords ak  (cost=0.42..8.63 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=19904)
                      Index Cond: ((k.name = name) AND (dm.market_id = market_id))
  ->  CTE Scan on latest_ranking_params  (cost=0.00..3174.88 rows=126995 width=108) (actual time=954.597..1604.052 rows=19904 loops=1)
Planning Time: 3.452 ms
Execution Time: 1618.781 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.480 ms	0.0 %
Bitmap Index Scan	1	0.029 ms	0.0 %
CTE Scan	1	1,604.052 ms	99.1 %
Hash	2	362.631 ms	22.4 %
Hash Join	1	1.570 ms	0.1 %
Hash Left Join	1	128.096 ms	7.9 %
HashAggregate	1	14.178 ms	0.9 %
Index Only Scan	1	0.003 ms	0.0 %
Index Only Scan Backward	1	119.424 ms	7.4 %
Index Scan	6	709.791 ms	43.9 %
Index Scan Backward	1	159.232 ms	9.8 %
Limit	3	39.808 ms	2.5 %
Materialize	1	1.467 ms	0.1 %
Nested Loop	5	4.002 ms	0.2 %
Nested Loop Left Join	3	19.823 ms	1.2 %
Seq Scan	1	1.522 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
adwords_keywords	1	159.232 ms	9.8 %
Index Scan Backward	1	159.232 ms	100.0 %
domains	2	0.015 ms	0.0 %
Index Only Scan	1	0.003 ms	20.0 %
Index Scan	1	0.012 ms	80.0 %
domains_keywords	1	0.480 ms	0.0 %
Bitmap Heap Scan	1	0.480 ms	100.0 %
domains_markets	1	0.191 ms	0.0 %
Index Scan	1	0.191 ms	100.0 %
keywords	1	1.522 ms	0.1 %
Seq Scan	1	1.522 ms	100.0 %
markets	2	0.830 ms	0.1 %
Index Scan	2	0.830 ms	100.0 %
multisample_rankings	3	828.182 ms	51.2 %
Index Only Scan Backward	1	119.424 ms	14.4 %
Index Scan	2	708.758 ms	85.6 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A95m : Optimization for: plan #nmjN

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats