explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CJSc

Settings
# exclusive inclusive rows x rows loops node
1. 2.425 32,878.410 ↑ 1.0 1 1

Aggregate (cost=304,207.90..304,207.91 rows=1 width=56) (actual time=32,878.410..32,878.410 rows=1 loops=1)

2. 0.695 32,867.054 ↓ 229.0 687 1

Nested Loop (cost=298,944.88..304,131.28 rows=3 width=60) (actual time=32,713.682..32,867.054 rows=687 loops=1)

  • Join Filter: (t3.customer_id = t5.id)
3. 40.472 32,853.993 ↓ 229.0 687 1

Hash Join (cost=298,944.58..304,130.14 rows=3 width=64) (actual time=32,712.603..32,853.993 rows=687 loops=1)

  • Hash Cond: (t6.id = t3.customer_id)
4. 1,367.303 32,808.520 ↑ 1.0 139,860 1

HashAggregate (cost=298,924.20..302,142.80 rows=143,049 width=44) (actual time=32,703.735..32,808.520 rows=139,860 loops=1)

  • Group Key: t6.id
5. 746.162 31,441.217 ↓ 1.0 967,621 1

Hash Join (cost=11,558.24..282,222.07 rows=954,407 width=25) (actual time=329.114..31,441.217 rows=967,621 loops=1)

  • Hash Cond: (t8.customer_id = t6.id)
6. 911.596 30,568.360 ↓ 1.0 967,621 1

Hash Join (cost=6,861.64..275,020.08 rows=954,407 width=25) (actual time=201.587..30,568.360 rows=967,621 loops=1)

  • Hash Cond: (t7.visit_id = t8.id)
7. 29,456.014 29,456.014 ↓ 1.0 967,621 1

Seq Scan on face t7 (cost=0.00..265,653.07 rows=954,407 width=25) (actual time=0.019..29,456.014 rows=967,621 loops=1)

8. 85.476 200.750 ↑ 1.0 233,695 1

Hash (cost=3,935.73..3,935.73 rows=234,073 width=8) (actual time=200.750..200.750 rows=233,695 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11177kB
9. 115.274 115.274 ↑ 1.0 233,695 1

Seq Scan on visit t8 (cost=0.00..3,935.73 rows=234,073 width=8) (actual time=0.006..115.274 rows=233,695 loops=1)

10. 51.291 126.695 ↑ 1.0 142,996 1

Hash (cost=2,908.49..2,908.49 rows=143,049 width=4) (actual time=126.694..126.695 rows=142,996 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7076kB
11. 75.404 75.404 ↑ 1.0 142,996 1

Seq Scan on customer t6 (cost=0.00..2,908.49 rows=143,049 width=4) (actual time=0.556..75.404 rows=142,996 loops=1)

12. 0.240 5.001 ↓ 703.0 703 1

Hash (cost=20.37..20.37 rows=1 width=20) (actual time=5.001..5.001 rows=703 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
13. 4.761 4.761 ↓ 703.0 703 1

Index Scan using visit_timestamp_idx on visit t3 (cost=0.42..20.37 rows=1 width=20) (actual time=2.479..4.761 rows=703 loops=1)

  • Index Cond: (("timestamp" >= '1567976400'::double precision) AND ("timestamp" <= '1568062799.99999905'::double precision))
  • Filter: ((duration > '0'::double precision) AND ((timezone('Europe/Kiev'::text, timezone('UTC'::text, ('1970-01-01 00:00:00'::timestamp without time zone + ("timestamp" * '00:00:01'::interval)))))::time without time zone >= '07:
  • Rows Removed by Filter: 49
14. 12.366 12.366 ↑ 1.0 1 687

Index Scan using customer_pkey on customer t5 (cost=0.29..0.37 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=687)

  • Index Cond: (id = t6.id)
  • Filter: (facility_id = 1)
15.          

SubPlan (forAggregate)

16. 0.687 7.557 ↓ 0.0 0 687

Limit (cost=10.29..15.23 rows=1 width=24) (actual time=0.011..0.011 rows=0 loops=687)

17. 6.870 6.870 ↓ 0.0 0 687

Index Scan using visit_customer_id on visit t4 (cost=0.42..10.29 rows=2 width=24) (actual time=0.010..0.010 rows=0 loops=687)

  • Index Cond: (customer_id = t3.customer_id)
  • Filter: ("timestamp" < t3."timestamp")
  • Rows Removed by Filter: 1
18. 0.000 1.374 ↓ 0.0 0 687

Limit (cost=5.36..10.29 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=687)

19. 1.374 1.374 ↓ 0.0 0 687

Index Scan using visit_customer_id on visit t2 (cost=0.42..10.29 rows=2 width=24) (actual time=0.002..0.002 rows=0 loops=687)

  • Index Cond: (customer_id = t3.customer_id)
  • Filter: ("timestamp" < t3."timestamp")
  • Rows Removed by Filter: 1
Planning time : 16.849 ms
Execution time : 32,883.060 ms