#	exclusive	inclusive	rows x	rows	loops	node
1.	1,097.668	2,919.249	↑ 33.3	6	1	→ GroupAggregate (cost=368,696.620..368,796.800 rows=200 width=19) (actual time=1,854.918..2,919.249 rows=6 loops=1) Group Key: foxtrot2019kilo_oscar11kilo_oscar28.two_charlie
2.	734.815	1,821.581	↓ 32.3	317,493	1	→ Sort (cost=368,696.620..368,721.160 rows=9,818 width=901) (actual time=1,667.914..1,821.581 rows=317,493 loops=1) Sort Key: foxtrot2019kilo_oscar11kilo_oscar28.two_charlie Sort Method: external merge Disk: 324,776kB
3.	21.149	1,086.766	↓ 32.3	317,493	1	→ Append (cost=0.300..368,045.630 rows=9,818 width=901) (actual time=0.015..1,086.766 rows=317,493 loops=1) Subplans Removed: 57
4.	253.842	253.842	↓ 32.7	66,713	1	→ Index Scan using xray on charlie (cost=0.430..78,667.160 rows=2,040 width=911) (actual time=0.015..253.842 rows=66,713 loops=1) Index Cond: ("india_three" >= (zulu_golf() - 'delta'::interval)) Filter: (((six_mike ->> 'zulu_sierra'::text) = ANY ('six_six'::text[])) AND (two_charlie = ANY ('kilo_three'::text[]))) Rows Removed by Filter: 451,304
5.	811.766	811.766	↓ 32.5	250,780	1	→ Seq Scan on six_delta (cost=0.000..289,231.290 rows=7,720 width=899) (actual time=0.025..811.766 rows=250,780 loops=1) Filter: (((six_mike ->> 'zulu_sierra'::text) = ANY ('six_six'::text[])) AND ("india_three" >= (zulu_golf() - 'delta'::interval)) AND (two_charlie = ANY ('kilo_three'::text[]))) Rows Removed by Filter: 1,662,294
6.	0.002	0.009	↓ 0.0	0	1	→ Bitmap Heap Scan on two_tango (cost=4.800..9.090 rows=1 width=64) (actual time=0.009..0.009 rows=0 loops=1) Recheck Cond: ((two_charlie = ANY ('kilo_three'::text[])) AND ("india_three" >= (zulu_golf() - 'delta'::interval))) Filter: ((six_mike ->> 'zulu_sierra'::text) = ANY ('six_six'::text[]))
7.	0.001	0.007	↓ 0.0	0	1	→ BitmapAnd (cost=4.800..4.800 rows=5 width=0) (actual time=0.007..0.007 rows=0 loops=1)
8.	0.006	0.006	↓ 0.0	0	1	→ Bitmap Index Scan on zulu_juliet (cost=0.000..2.080 rows=15 width=0) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: (two_charlie = ANY ('kilo_three'::text[]))
9.	0.000	0.000	↓ 0.0		0	→ Bitmap Index Scan on india_two (cost=0.000..2.470 rows=163 width=0) (never executed) Index Cond: ("india_three" >= (zulu_golf() - 'delta'::interval))

Planning time	:	3.648 ms
Execution time	:	2,957.596 ms

GroupAggregate  (cost=368696.620..368796.800 rows=200 width=19) (actual time=1854.918..2919.249 rows=6 loops=1)
    Group Key: foxtrot2019kilo_oscar11kilo_oscar28.two_charlie
  ->  Sort  (cost=368696.620..368721.160 rows=9818 width=901) (actual time=1667.914..1821.581 rows=317493 loops=1)
          Sort Key: foxtrot2019kilo_oscar11kilo_oscar28.two_charlie
          Sort Method: external merge  Disk: 324776kB
        ->  Append  (cost=0.300..368045.630 rows=9818 width=901) (actual time=0.015..1086.766 rows=317493 loops=1)
                Subplans Removed: 57
              ->  Index Scan using xray on charlie  (cost=0.430..78667.160 rows=2040 width=911) (actual time=0.015..253.842 rows=66713 loops=1)
                      Index Cond: ("india_three" >= (zulu_golf() - 'delta'::interval))
                      Filter: (((six_mike ->> 'zulu_sierra'::text) = ANY ('six_six'::text[])) AND (two_charlie = ANY ('kilo_three'::text[])))
                      Rows Removed by Filter: 451304
              ->  Seq Scan on six_delta  (cost=0.000..289231.290 rows=7720 width=899) (actual time=0.025..811.766 rows=250780 loops=1)
                      Filter: (((six_mike ->> 'zulu_sierra'::text) = ANY ('six_six'::text[])) AND ("india_three" >= (zulu_golf() - 'delta'::interval)) AND (two_charlie = ANY ('kilo_three'::text[])))
                      Rows Removed by Filter: 1662294
              ->  Bitmap Heap Scan on two_tango  (cost=4.800..9.090 rows=1 width=64) (actual time=0.009..0.009 rows=0 loops=1)
                      Recheck Cond: ((two_charlie = ANY ('kilo_three'::text[])) AND ("india_three" >= (zulu_golf() - 'delta'::interval)))
                      Filter: ((six_mike ->> 'zulu_sierra'::text) = ANY ('six_six'::text[]))
                    ->  BitmapAnd  (cost=4.800..4.800 rows=5 width=0) (actual time=0.007..0.007 rows=0 loops=1)
                          ->  Bitmap Index Scan on zulu_juliet  (cost=0.000..2.080 rows=15 width=0) (actual time=0.006..0.006 rows=0 loops=1)
                                  Index Cond: (two_charlie = ANY ('kilo_three'::text[]))
                          ->  Bitmap Index Scan on india_two  (cost=0.000..2.470 rows=163 width=0) (never executed)
                                  Index Cond: ("india_three" >= (zulu_golf() - 'delta'::interval))
Planning time: 3.648 ms
Execution time: 2957.596 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	21.149 ms	0.7 %
Bitmap Heap Scan	1	0.002 ms	0.0 %
Bitmap Index Scan	2	0.006 ms	0.0 %
BitmapAnd	1	0.001 ms	0.0 %
GroupAggregate	1	1,097.668 ms	37.6 %
Index Scan	1	253.842 ms	8.7 %
Seq Scan	1	811.766 ms	27.8 %
Sort	1	734.815 ms	25.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
charlie	1	253.842 ms	8.7 %
Index Scan	1	253.842 ms	100.0 %
six_delta	1	811.766 ms	27.8 %
Seq Scan	1	811.766 ms	100.0 %
two_tango	1	0.002 ms	0.0 %
Bitmap Heap Scan	1	0.002 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ld2K : Optimization for: plan #Obq7

Color mode:

Visible columns:

Optimization path:

Optimization(s) for this plan:

Per node type stats

Per table stats