explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LcUa : stat1

Settings
# exclusive inclusive rows x rows loops node
1. 2,955.231 255,661.658 ↓ 33.4 167 1

WindowAgg (cost=13,472.38..13,491.45 rows=5 width=224) (actual time=247,228.237..255,661.658 rows=167 loops=1)

2. 5,497.335 252,706.427 ↓ 33.4 167 1

GroupAggregate (cost=13,472.38..13,485.00 rows=5 width=175) (actual time=247,210.707..252,706.427 rows=167 loops=1)

  • Group Key: lookup.v, ks.v, (concat(nip.v, ' (', (CASE WHEN (count(*) = 1) THEN a.v ELSE concat(count(*), 'x', a.v) END), ')')), (to_char(date_trunc('month'::text, (((((lower(f.utr) - '12:00:00'::interval) + ((((lon.v)::numeric / '15'::numeric))::double precision * '01:00:00'::interval)))::date))::timestamp with time zone), 'YYYY.MM'::text))
  • Group Key: lookup.v, ks.v, (concat(nip.v, ' (', (CASE WHEN (count(*) = 1) THEN a.v ELSE concat(count(*), 'x', a.v) END), ')'))
  • Group Key: lookup.v, ks.v
  • Group Key: lookup.v
  • Group Key: ()
3. 238.021 247,209.092 ↓ 2,818.0 2,818 1

Sort (cost=13,472.38..13,472.38 rows=1 width=139) (actual time=247,207.961..247,209.092 rows=2,818 loops=1)

  • Sort Key: lookup.v, ks.v, (concat(nip.v, ' (', (CASE WHEN (count(*) = 1) THEN a.v ELSE concat(count(*), 'x', a.v) END), ')')), (to_char(date_trunc('month'::text, (((((lower(f.utr) - '12:00:00'::interval) + ((((lon.v)::numeric / '15'::numeric))::double precision * '01:00:00'::interval)))::date))::timestamp with time zone), 'YYYY.MM'::text))
  • Sort Method: quicksort Memory: 3900kB
4. 19.204 246,971.071 ↓ 2,818.0 2,818 1

Nested Loop Left Join (cost=12,364.06..13,472.37 rows=1 width=139) (actual time=114.665..246,971.071 rows=2,818 loops=1)

5. 6.955 239,379.901 ↓ 2,818.0 2,818 1

Nested Loop (cost=12,213.26..13,321.51 rows=1 width=116) (actual time=110.533..239,379.901 rows=2,818 loops=1)

6. 5.984 239,364.492 ↓ 2,818.0 2,818 1

Nested Loop Left Join (cost=12,212.98..13,320.86 rows=1 width=109) (actual time=110.525..239,364.492 rows=2,818 loops=1)

7. 7.704 238,062.228 ↓ 2,818.0 2,818 1

Nested Loop (cost=12,212.71..13,287.55 rows=1 width=101) (actual time=109.950..238,062.228 rows=2,818 loops=1)

  • Join Filter: (loc.dtr @> (f.tm)::date)
8. 26.348 236,704.702 ↓ 2,818.0 2,818 1

Nested Loop (cost=12,212.44..13,255.77 rows=1 width=101) (actual time=109.393..236,704.702 rows=2,818 loops=1)

9. 1.031 107.254 ↓ 2,818.0 2,818 1

Nested Loop (cost=667.50..1,397.99 rows=1 width=70) (actual time=87.275..107.254 rows=2,818 loops=1)

10. 1.960 1.960 ↑ 1.0 1 1

Index Scan using t_obsgroup_pk on t_obsgroup (cost=0.14..33.16 rows=1 width=43) (actual time=1.956..1.960 rows=1 loops=1)

  • Index Cond: (gro = 59)
  • Filter: aclget(acc, 1)
11. 19.205 104.263 ↓ 2,818.0 2,818 1

Bitmap Heap Scan on t_files f (cost=667.36..1,364.82 rows=1 width=70) (actual time=85.316..104.263 rows=2,818 loops=1)

  • Recheck Cond: ((t_obsgroup.members && tels) AND (tm > '2019-05-01 00:00:00'::timestamp without time zone))
  • Filter: (((ftype)::text = ANY ('{om,tc,mp}'::text[])) AND (utr && '["2019-05-01 00:00:00","2019-10-01 00:00:00")'::tsrange) AND ((state & 16) = 0))
  • Rows Removed by Filter: 2926
  • Heap Blocks: exact=2120
12. 0.495 85.058 ↓ 0.0 0 1

BitmapAnd (cost=667.36..667.36 rows=191 width=0) (actual time=85.058..85.058 rows=0 loops=1)

13. 83.217 83.217 ↓ 28.0 57,020 1

Bitmap Index Scan on t_files_tels_ix (cost=0.00..115.54 rows=2,034 width=0) (actual time=83.217..83.217 rows=57,020 loops=1)

  • Index Cond: (t_obsgroup.members && tels)
14. 1.346 1.346 ↓ 1.0 19,275 1

Bitmap Index Scan on t_files_tm_ix (cost=0.00..551.57 rows=19,087 width=0) (actual time=1.346..1.346 rows=19,275 loops=1)

  • Index Cond: (tm > '2019-05-01 00:00:00'::timestamp without time zone)
15. 194.442 236,571.100 ↑ 1.0 1 2,818

Hash Join (cost=11,544.93..11,857.76 rows=1 width=80) (actual time=48.838..83.950 rows=1 loops=2,818)

  • Hash Cond: (nip.dev = lon.dev)
  • Join Filter: (((f.tels)[1] = nip.dev) AND (nip.dtr @> (f.tm)::date))
16. 231,566.332 231,602.966 ↓ 29.5 177 2,818

Bitmap Heap Scan on t_devattr nip (cost=4.22..317.02 rows=6 width=20) (actual time=0.482..82.187 rows=177 loops=2,818)

  • Recheck Cond: ((atype = 20010) AND unq)
  • Filter: (aclget(acc, 1) OR pg_has_role('__ncsat_sysadmins__'::name, 'member'::text))
  • Heap Blocks: exact=11272
17. 36.634 36.634 ↓ 16.1 177 2,818

Bitmap Index Scan on t_devattr_atype_v_excl (cost=0.00..4.22 rows=11 width=0) (actual time=0.013..0.013 rows=177 loops=2,818)

  • Index Cond: (atype = 20010)
18. 5.636 4,773.692 ↑ 1.0 1 2,818

Hash (cost=11,540.70..11,540.70 rows=1 width=60) (actual time=1.694..1.694 rows=1 loops=2,818)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 5.636 4,768.056 ↑ 1.0 1 2,818

Nested Loop (cost=11,507.64..11,540.70 rows=1 width=60) (actual time=1.667..1.692 rows=1 loops=2,818)

20. 273.346 3,263.244 ↑ 1.0 1 2,818

Aggregate (cost=11,507.37..11,507.38 rows=1 width=40) (actual time=1.158..1.158 rows=1 loops=2,818)

21. 0.000 2,989.898 ↑ 2.2 531 2,818

Nested Loop (cost=1.00..11,495.68 rows=1,168 width=4) (actual time=0.015..1.061 rows=531 loops=2,818)

22. 332.524 332.524 ↑ 2.3 531 2,818

Index Scan using ix1_t_nip_res on t_nip_res (cost=0.56..996.75 rows=1,246 width=8) (actual time=0.008..0.118 rows=531 loops=2,818)

  • Index Cond: (file_id = f.fid)
23. 2,990.006 2,990.006 ↑ 1.0 1 1,495,003

Index Scan using pk_t_resbinds on t_resbinds (cost=0.44..8.43 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=1,495,003)

  • Index Cond: (trans_id = t_nip_res.trans_id)
24. 1,499.176 1,499.176 ↑ 1.0 1 2,818

Index Scan using t_devattr_dev_atype_excl on t_devattr lon (cost=0.27..33.30 rows=1 width=20) (actual time=0.507..0.532 rows=1 loops=2,818)

  • Index Cond: ((dev = (f.tels)[1]) AND (atype = 21005))
  • Filter: ((aclget(acc, 1) OR pg_has_role('__ncsat_sysadmins__'::name, 'member'::text)) AND (dtr @> (f.tm)::date))
  • Rows Removed by Filter: 0
25. 1,349.822 1,349.822 ↑ 1.0 1 2,818

Index Scan using t_devattr_dev_atype_excl on t_devattr loc (cost=0.27..31.77 rows=1 width=20) (actual time=0.478..0.479 rows=1 loops=2,818)

  • Index Cond: ((dev = lon.dev) AND (atype = 21002))
  • Filter: (aclget(acc, 1) OR pg_has_role('__ncsat_sysadmins__'::name, 'member'::text))
26. 1,296.280 1,296.280 ↑ 1.0 1 2,818

Index Scan using t_devattr_dev_atype_excl on t_devattr ks (cost=0.27..33.30 rows=1 width=20) (actual time=0.459..0.460 rows=1 loops=2,818)

  • Index Cond: (((f.tels)[1] = dev) AND (atype = 20022))
  • Filter: ((aclget(acc, 1) OR pg_has_role('__ncsat_sysadmins__'::name, 'member'::text)) AND (dtr @> (f.tm)::date))
27. 8.454 8.454 ↑ 1.0 1 2,818

Index Scan using t_attrtypelist_pk on t_attrtypelist lookup (cost=0.28..0.65 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=2,818)

  • Index Cond: ((loc.v)::integer = rec)
28. 8.454 7,571.966 ↑ 1.0 1 2,818

GroupAggregate (cost=150.80..150.83 rows=1 width=40) (actual time=2.687..2.687 rows=1 loops=2,818)

  • Group Key: a.v
29. 8.454 7,563.512 ↑ 1.0 1 2,818

Sort (cost=150.80..150.81 rows=1 width=8) (actual time=2.684..2.684 rows=1 loops=2,818)

  • Sort Key: a.v
  • Sort Method: quicksort Memory: 25kB
30. 11.101 7,555.058 ↑ 1.0 1 2,818

Nested Loop (cost=4.85..150.79 rows=1 width=8) (actual time=1.980..2.681 rows=1 loops=2,818)

31. 18.610 5,562.732 ↑ 1.0 1 2,818

Nested Loop (cost=4.57..119.01 rows=1 width=8) (actual time=1.503..1.974 rows=1 loops=2,818)

32. 3,564.770 3,573.224 ↓ 2.0 2 2,818

Bitmap Heap Scan on t_devmembers m1 (cost=4.30..84.98 rows=1 width=4) (actual time=0.517..1.268 rows=2 loops=2,818)

  • Recheck Cond: (dev = (f.tels)[1])
  • Filter: (aclget_devmembers(dev, 1) OR pg_has_role('__ncsat_sysadmins__'::name, 'member'::text))
  • Heap Blocks: exact=6415
33. 8.454 8.454 ↑ 1.5 2 2,818

Bitmap Index Scan on t_devmembers_dev_ix (cost=0.00..4.30 rows=3 width=0) (actual time=0.003..0.003 rows=2 loops=2,818)

  • Index Cond: (dev = (f.tels)[1])
34. 1,970.898 1,970.898 ↑ 1.0 1 6,989

Index Scan using t_device_pk on t_device dm (cost=0.28..33.30 rows=1 width=4) (actual time=0.282..0.282 rows=1 loops=6,989)

  • Index Cond: (dev = m1.member)
  • Filter: ((tp = 9) AND (aclget(acc, 1) OR pg_has_role('__ncsat_sysadmins__'::name, 'member'::text)))
  • Rows Removed by Filter: 0
35. 1,981.225 1,981.225 ↑ 1.0 1 4,171

Index Scan using t_devattr_dev_atype_excl on t_devattr a (cost=0.27..31.77 rows=1 width=12) (actual time=0.475..0.475 rows=1 loops=4,171)

  • Index Cond: ((dev = dm.dev) AND (atype = 91001))
  • Filter: ((aclget(acc, 1) OR pg_has_role('__ncsat_sysadmins__'::name, 'member'::text)) AND (dtr @> (f.tm)::date))