# | exclusive | inclusive | rows x | rows | loops | node |
---|---|---|---|---|---|---|
1. | 0.021 | 4.929 | ↑ 2.4 | 43 | 1 |
→
Limit (cost=1,344.54..1,943.53 rows=105 width=472) (actual time=3.167..4.929 rows=43 loops=1) |
2. | 1.557 | 4.908 | ↑ 2.4 | 43 | 1 |
→
Hash Join (cost=1,344.54..1,943.53 rows=105 width=472) (actual time=3.166..4.908 rows=43 loops=1)
|
3. | 0.041 | 2.491 | ↑ 2.4 | 43 | 1 |
→
Hash Join (cost=1,291.07..1,781.77 rows=105 width=619) (actual time=2.172..2.491 rows=43 loops=1)
|
4. | 0.111 | 1.635 | ↑ 2.4 | 43 | 1 |
→
Nested Loop (cost=1,237.59..1,726.86 rows=105 width=597) (actual time=1.349..1.635 rows=43 loops=1)
|
5. | 0.070 | 1.481 | ↑ 2.4 | 43 | 1 |
→
Nested Loop Left Join (cost=1,237.59..1,717.92 rows=105 width=509) (actual time=1.342..1.481 rows=43 loops=1)
|
6. | 0.087 | 1.411 | ↑ 2.4 | 43 | 1 |
→
Bitmap Heap Scan on entity_agg d (cost=1,237.59..1,715.28 rows=105 width=291) (actual time=1.334..1.411 rows=43 loops=1)
|
7. | 1.324 | 1.324 | ↑ 2.9 | 43 | 1 |
→
Bitmap Index Scan on idx_entity_agg_coords_dates_impact (cost=0.00..1,237.57 rows=125 width=0) (actual time=1.324..1.324 rows=43 loops=1)
|
8. | 0.000 | 0.000 | ↓ 0.0 | 0 | 43 |
→
Materialize (cost=0.00..1.07 rows=1 width=222) (actual time=0.000..0.000 rows=0 loops=43) |
9. | 0.005 | 0.005 | ↓ 0.0 | 0 | 1 |
→
Seq Scan on eton_impact_lang ei_lang (cost=0.00..1.06 rows=1 width=222) (actual time=0.005..0.005 rows=0 loops=1)
|
10. | 0.039 | 0.043 | ↑ 1.0 | 5 | 43 |
→
Materialize (cost=0.00..1.07 rows=5 width=92) (actual time=0.000..0.001 rows=5 loops=43) |
11. | 0.004 | 0.004 | ↑ 1.0 | 5 | 1 |
→
Seq Scan on eton_impact ei (cost=0.00..1.05 rows=5 width=92) (actual time=0.002..0.004 rows=5 loops=1) |
12. | 0.403 | 0.815 | ↑ 1.0 | 1,310 | 1 |
→
Hash (cost=37.10..37.10 rows=1,310 width=26) (actual time=0.815..0.815 rows=1,310 loops=1)
|
13. | 0.412 | 0.412 | ↑ 1.0 | 1,310 | 1 |
→
Seq Scan on orgref publisher (cost=0.00..37.10 rows=1,310 width=26) (actual time=0.002..0.412 rows=1,310 loops=1) |
14. | 0.391 | 0.860 | ↑ 1.0 | 1,310 | 1 |
→
Hash (cost=37.10..37.10 rows=1,310 width=26) (actual time=0.860..0.860 rows=1,310 loops=1)
|
15. | 0.469 | 0.469 | ↑ 1.0 | 1,310 | 1 |
→
Seq Scan on orgref resporg (cost=0.00..37.10 rows=1,310 width=26) (actual time=0.004..0.469 rows=1,310 loops=1) |
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1344.54..1943.53 rows=105 width=472) (actual time=3.167..4.929 rows=43 loops=1) -> Hash Join (cost=1344.54..1943.53 rows=105 width=472) (actual time=3.166..4.908 rows=43 loops=1) Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref) -> Hash Join (cost=1291.07..1781.77 rows=105 width=619) (actual time=2.172..2.491 rows=43 loops=1) Hash Cond: (d.publisher_orgref = publisher.swa_org_ref) -> Nested Loop (cost=1237.59..1726.86 rows=105 width=597) (actual time=1.349..1.635 rows=43 loops=1) Join Filter: (d.impact_score = ei.impact_score) Rows Removed by Join Filter: 172 -> Nested Loop Left Join (cost=1237.59..1717.92 rows=105 width=509) (actual time=1.342..1.481 rows=43 loops=1) Join Filter: (d.impact_score = ei_lang.impact_score) -> Bitmap Heap Scan on entity_agg d (cost=1237.59..1715.28 rows=105 width=291) (actual time=1.334..1.411 rows=43 loops=1) Recheck Cond: ((location_point_easting >= 300000) AND (location_point_easting <= 350000) AND (location_point_northing >= 500000) AND (location_point_northing <= 550000) AND (start_date <= to_timestamp('31/11/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('01/11/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))) Filter: ((entity_type = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[]))) Heap Blocks: exact=34 -> Bitmap Index Scan on idx_entity_agg_coords_dates_impact (cost=0.00..1237.57 rows=125 width=0) (actual time=1.324..1.324 rows=43 loops=1) Index Cond: ((location_point_easting >= 300000) AND (location_point_easting <= 350000) AND (location_point_northing >= 500000) AND (location_point_northing <= 550000) AND (start_date <= to_timestamp('31/11/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('01/11/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))) -> Materialize (cost=0.00..1.07 rows=1 width=222) (actual time=0.000..0.000 rows=0 loops=43) -> Seq Scan on eton_impact_lang ei_lang (cost=0.00..1.06 rows=1 width=222) (actual time=0.005..0.005 rows=0 loops=1) Filter: ((code)::text = 'en'::text) Rows Removed by Filter: 5 -> Materialize (cost=0.00..1.07 rows=5 width=92) (actual time=0.000..0.001 rows=5 loops=43) -> Seq Scan on eton_impact ei (cost=0.00..1.05 rows=5 width=92) (actual time=0.002..0.004 rows=5 loops=1) -> Hash (cost=37.10..37.10 rows=1310 width=26) (actual time=0.815..0.815 rows=1310 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 82kB -> Seq Scan on orgref publisher (cost=0.00..37.10 rows=1310 width=26) (actual time=0.002..0.412 rows=1310 loops=1) -> Hash (cost=37.10..37.10 rows=1310 width=26) (actual time=0.860..0.860 rows=1310 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 82kB -> Seq Scan on orgref resporg (cost=0.00..37.10 rows=1310 width=26) (actual time=0.004..0.469 rows=1310 loops=1) Planning time: 0.959 ms Execution time: 5.034 ms 30 rows selected.
node type | count | sum of times | % of query |
---|---|---|---|
Bitmap Heap Scan | 1 | 0.087 ms | 1.8 % |
Bitmap Index Scan | 1 | 1.324 ms | 26.9 % |
Hash | 2 | 0.794 ms | 16.1 % |
Hash Join | 2 | 1.598 ms | 32.4 % |
Limit | 1 | 0.021 ms | 0.4 % |
Materialize | 2 | 0.039 ms | 0.8 % |
Nested Loop | 1 | 0.111 ms | 2.3 % |
Nested Loop Left Join | 1 | 0.070 ms | 1.4 % |
Seq Scan | 4 | 0.890 ms | 18.1 % |
Table name | Scan count | Total time | % of query |
---|---|---|---|
scan type | count | sum of times | % of table |
entity_agg | 1 | 0.087 ms | 1.8 % |
Bitmap Heap Scan | 1 | 0.087 ms | 100.0 % |
eton_impact | 1 | 0.004 ms | 0.1 % |
Seq Scan | 1 | 0.004 ms | 100.0 % |
eton_impact_lang | 1 | 0.005 ms | 0.1 % |
Seq Scan | 1 | 0.005 ms | 100.0 % |
orgref | 2 | 0.881 ms | 17.9 % |
Seq Scan | 2 | 0.881 ms | 100.0 % |