explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q3UO

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 136,636.393 ↑ 1.0 1 1

Aggregate (cost=55,269,751.79..55,269,751.80 rows=1 width=32) (actual time=136,636.393..136,636.393 rows=1 loops=1)

  • Output: json_agg(json_build_object('id', r.id, 'data', COALESCE(json_strip_nulls(COALESCE((json_agg(json_build_object('dimension', n.dimension, 'on_date', n.on_date, 'metric', n.metric) ORDER BY n.priority) FILTER (WHERE ((n.dimension IS NOT NULL) OR (n.on_date IS NOT NULL) OR (n.metric <> '0'::numeric)))), (json_agg(json_build_object('dimension', (to_jsonb(r.*) -> ('dimension'::text || (r.id)::text)), 'on_date', (to_jsonb(r.*) -> ('on_date'::text || (r.id)::text)), 'metric', (to_jsonb(r.*) -> ('metric'::text || (r.id)::text))) ORDER BY (to_jsonb(r.*) -> ('priority'::text || (r.id)::text))) FILTER (WHERE ((NOT r.is_total) AND (((to_jsonb(r.*) ->> ('dimension'::text || (r.id)::text)) IS NOT NULL) OR ((to_jsonb(r.*) ->> ('on_date'::text || (r.id)::text)) IS NOT NULL) OR ((to_jsonb(r.*) ->> ('metric'::text || (r.id)::text)) <> '0'::text))))))), '[]'::json), 'total', (array_agg(json_build_object('total_metric', (to_jsonb(r.*) -> ('metric'::text || (r.id)::text)))) FILTER (WHERE r.is_total))[1]))
  • Buffers: shared hit=4,412,260 read=13,220 dirtied=6, temp read=274,182 written=328,813
2.          

CTE w_source_query

3. 18,373.851 97,232.674 ↓ 2.6 6,464,066 1

Hash Full Join (cost=3,340,652.26..54,178,643.51 rows=2,471,162 width=80) (actual time=65,059.418..97,232.674 rows=6,464,066 loops=1)

  • Output: COALESCE((COALESCE((COALESCE(ac.name, lc(ac.lc_name, 74))), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))))), "*SELECT* 1".ac_name), (c.start_time)::timestamp without time zone, (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END), COALESCE((COALESCE(c.site_id, s_15.site_id)), "*SELECT* 1".site_id), (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END)
  • Hash Cond: (((COALESCE(c.site_id, s_15.site_id)) = "*SELECT* 1".site_id) AND ((COALESCE(c.ac_id, s_15.ac_id)) = "*SELECT* 1".ac_id) AND ((CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(pc.id, '-1'::integer)) ELSE NULL::integer END) = "*SELECT* 1".pc_id) AND ((CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(ad.id, '-1'::integer)) ELSE NULL::integer END) = "*SELECT* 1".ad_id) AND ((CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(kw.id, '-1'::integer)) ELSE NULL::integer END) = "*SELECT* 1".kw_id) AND ((CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(auditory.id, '-1'::integer)) ELSE NULL::integer END) = "*SELECT* 1".auditory_id) AND ((COALESCE(s_15.ppc_place, ''::text)) = "*SELECT* 1".place) AND ((lc(device_type.lc_name, 74)) = "*SELECT* 1".device_type) AND ((CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN s_15.ppc_region_id ELSE NULL::integer END) = "*SELECT* 1".region_id))
  • Join Filter: ((NOT (((s_15.ppc_position_type)::text) IS DISTINCT FROM "*SELECT* 1".position_type)) AND (s_15.start_time = "*SELECT* 1".day))
  • Filter: CASE WHEN ((COALESCE((COALESCE(c.site_id, s_15.site_id)), "*SELECT* 1".site_id) = ANY ('{4179,4177,4171,4167,67,103,282,1023,2156,2784,3242,3248,3606,3893,3904,3908,3922,3927,3928,3960,3996,3999,4008,4077,4105}'::integer[])) AND ((COALESCE((COALESCE(c.ac_id, s_15.ac_id)), "*SELECT* 1".ac_id) = ANY ('{6562,6560,6558,6556,6552,6494,6492,6490,6488,6486,6484,6464,6432,6428,6424,6422,6420,6418,6414,6412,6410,6408,6406,6404,6396,6394,6384,6334,6332,6330,6316,6310,6282,6280,6278,6276,6274,6272,6270,6268,6266,6264,6262,6260,6258,6256,6254,6252,6250,6248,6246,6244,6242,6240,6236,6228,6226,6222,6216,6214,6212,6186,6184,6182,6056,6052,6036,6034,6032,6028,5958,5956,5954,5952,5950,5948,5928,5926,5924,5922,5916,5914,5912,5910,5908,5906,5904,5902,5888,5872,5870,5866,5862,5860,5852,5846,4268,4751,4264,4498,4643,4720,4270,4494,5376,4632,1359,5338,3767,4263,4196,3769,5296,4743,4599,362,4648,3466,3691,2323,4500,4724,4638,4207,4495,4749,4282,229,3676,2189,2286,4520,4533,4928,5396,4257,4499,2261,5286,4232,340,4218,4949,230,4269,5394,4224,4645,4400,4256,4966,4186,5124,4727,5216,4283,4721,3669,4652,4175,4285,5190,2325,4238,4279,3454,4485,2257,4639,4725,231,4541,4214,4501,4273,4529,4236,1357,3644,4653,4252,149,4397,4855,4492,4491,4531,4276,3480,4508,4598,4202,4580,4445,4290,4482,4756,2345,4228,4287,4274,4451,2225,4221,4753,2153,4217,4234,2312,4235,3625,3711,4503,4254,3717,4206,4738,2227,5292,3705,4444,4657,5378,5260,4726,4142,4497,4293,4208,3768,4201,4280,4203,4515,4522,4640,4536,4594,4752,4246,4530,4722,5374,4446,5147,2202,4200,4571,5114,4891,147,3687,2348,3288,4510,4286,4488,4190,4641,4259,4229,4449,3646,4333,4723,5364,2307,4332,4199,4642,4487,4407,4448,4827,4213,4493,4271,4549,4544,4204,4239,4489,4209,2240,4244,4262,5390,4278,2258,5370,4754,4745,5262,2319,4248,4210,4550,2343,4912,4523,5294,4260,4249,5304,4513,4748,2141,4719,4516,4929,4552,2280,3453,4253,5298,4525,3626,3805,4856,4651,2315,4230,4528,4362,4195,5318,3919,5626,2984,4452,3309,2384,4750,4892,5512,4266,3940,4524,4890,4507,4744,2259,4506,4755,4255,4505,2239,4655,4450,4527,4443,4215,4288,4967,4323,4739,5316,4247,6046,6044,6042,6040,6038,4650,2264,4646,4553,4521,4197,4261,3652,5226,4277,5382,4540,4219,2377,4245,2293,3325,4291,4267,4490,2354,4237,4597,4281,228,4188,4439,4517,5290,4504,4654,4442,4191,4440,4649,4216,4660,4484,4518,4187,4272,4289,4220,4656,4438,5168,2324,2311,4284,2282,4331,4532,4194,4496,2340,4762,4647,4205,1208,4502,4192,4747,5392,4447,4454,4509,2387,1210,2326,4512,5288,1209,4441,4742,4258,5258,219,341,4292,4240,4486,5146,254,3763,4250,3629,4519,4644,4198,4526,3218,4275,4265,4453,4514,4225,5690,5720,6554,6480,6458,6434,6430,6356,6354,6352,6348,6318,5942,5940,5938,5932,5930,5366,2358,5372,5330,5582,5332,4994,5328,5312,5324,5586,5314,5224,2355,5368,4980,5222,5184,5322,5188,4998,4995,4338,4999,5386,5648,4979,5326,5650,5652,5654,5656,5658,5660,5662,5664,5666}'::integer[])) OR (((COALESCE((COALESCE(c.ac_id, s_15.ac_id)), "*SELECT* 1".ac_id) IS NULL) OR (COALESCE((COALESCE(c.ac_id, s_15.ac_id)), "*SELECT* 1".ac_id) = '-1'::integer)) AND (COALESCE((COALESCE(c.site_id, s_15.site_id)), "*SELECT* 1".site_id) = ANY ('{4179,4177,4171,4167,282,3999,4077,1023,3242,3908,3927,3922,67,3248,2156,4105,3904,3606,3893,3996,3960,103,3928,4008,2784}'::integer[]))))) THEN true ELSE NULL::boolean END
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=274,208
4. 10,228.838 78,006.485 ↓ 1.2 6,085,976 1

WindowAgg (cost=3,317,516.69..3,614,056.19 rows=4,942,325 width=647) (actual time=64,206.963..78,006.485 rows=6,085,976 loops=1)

  • Output: NULL::integer, NULL::analytics.communication_type_enum[], NULL::text, COALESCE(c.site_id, s_15.site_id), NULL::text, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), c.start_time, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::bigint, NULL::text, NULL::text, NULL::text, NULL::text, (COALESCE(s_15.ppc_place, ''::text)), (s_15.ppc_position_type)::text, NULL::text, (lc(device_type.lc_name, 74)), NULL::text, COALESCE(c.ac_id, s_15.ac_id), COALESCE((COALESCE(ac.name, lc(ac.lc_name, 74))), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74)))), s_15.start_time, (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END), CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(pc.id, '-1'::integer)) ELSE NULL::integer END, CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(ad.id, '-1'::integer)) ELSE NULL::integer END, CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(kw.id, '-1'::integer)) ELSE NULL::integer END, CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN (COALESCE(auditory.id, '-1'::integer)) ELSE NULL::integer END, CASE WHEN ((row_number() OVER (?) = 1) AND (s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)) THEN s_15.ppc_region_id ELSE NULL::integer END, NULL::integer, NULL::boolean, NULL::integer, NULL::boolean, NULL::integer, NULL::numeric(64,2), NULL::numeric, s_15.id
  • Buffers: shared hit=4,404,530 read=13,220 dirtied=6, temp read=274,182 written=274,208
5. 9,714.121 67,777.647 ↓ 1.2 6,085,976 1

Sort (cost=3,317,516.69..3,329,872.50 rows=4,942,325 width=228) (actual time=64,206.802..67,777.647 rows=6,085,976 loops=1)

  • Output: s_15.id, c.site_id, s_15.site_id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), c.start_time, (COALESCE(s_15.ppc_place, ''::text)), s_15.ppc_position_type, (lc(device_type.lc_name, 74)), c.ac_id, s_15.ac_id, (COALESCE(ac.name, lc(ac.lc_name, 74))), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))), s_15.start_time, (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer)), s_15.ppc_region_id
  • Sort Key: s_15.id
  • Sort Method: external merge Disk: 904,872kB
  • Buffers: shared hit=4,404,521 read=13,220 dirtied=6, temp read=274,182 written=274,208
6. 7,539.153 58,063.526 ↓ 1.2 6,085,976 1

Hash Full Join (cost=1,254,364.79..2,459,114.25 rows=4,942,325 width=228) (actual time=26,013.246..58,063.526 rows=6,085,976 loops=1)

  • Output: s_15.id, c.site_id, s_15.site_id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), c.start_time, (COALESCE(s_15.ppc_place, ''::text)), s_15.ppc_position_type, (lc(device_type.lc_name, 74)), c.ac_id, s_15.ac_id, (COALESCE(ac.name, lc(ac.lc_name, 74))), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))), s_15.start_time, (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer)), s_15.ppc_region_id
  • Hash Cond: (s.id = s_15.id)
  • Filter: ((c.id IS NOT NULL) OR ((s_15.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone)))
  • Rows Removed by Filter: 260,088
  • Buffers: shared hit=4,404,521 read=13,220 dirtied=6, temp read=161,073 written=161,073
7. 1,187.331 27,437.909 ↑ 1.0 3,681,856 1

Hash Left Join (cost=402,420.27..1,303,519.84 rows=3,813,769 width=96) (actual time=2,924.327..27,437.909 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, s.id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), (COALESCE(ac.name, lc(ac.lc_name, 74))), (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END)
  • Inner Unique: true
  • Hash Cond: (c.site_id = ss.id)
  • Buffers: shared hit=3,994,068 read=13,220 dirtied=6, temp read=50,541 written=50,541
8. 1,393.821 26,250.088 ↑ 1.0 3,681,856 1

Nested Loop Left Join (cost=402,356.09..1,293,425.42 rows=3,813,769 width=96) (actual time=2,923.817..26,250.088 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, s.id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), (COALESCE(ac.name, lc(ac.lc_name, 74))), (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END)
  • Buffers: shared hit=3,994,054 read=13,220 dirtied=6, temp read=50,541 written=50,541
9. 1,696.507 24,856.267 ↑ 1.0 3,681,856 1

Nested Loop Left Join (cost=402,356.09..1,179,012.35 rows=3,813,769 width=125) (actual time=2,923.815..24,856.267 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, c.communication_type, c.is_lost, c.is_not_goal, c.person_id, c.communication_number, c.ani, s.id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), (COALESCE(ac.name, lc(ac.lc_name, 74))), (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END)
  • Buffers: shared hit=3,994,054 read=13,220 dirtied=6, temp read=50,541 written=50,541
10. 1,215.458 23,159.760 ↑ 1.0 3,681,856 1

Hash Left Join (cost=402,356.09..1,064,599.28 rows=3,813,769 width=125) (actual time=2,923.811..23,159.760 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, c.communication_type, c.is_lost, c.is_not_goal, c.person_id, c.communication_number, c.ani, s.id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), (COALESCE(ac.name, lc(ac.lc_name, 74))), (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END)
  • Inner Unique: true
  • Hash Cond: ((c.id = sl.communication_id) AND (c.communication_type = sl.communication_type))
  • Buffers: shared hit=3,994,054 read=13,220 dirtied=6, temp read=50,541 written=50,541
11. 1,254.916 21,939.684 ↑ 1.0 3,681,856 1

Hash Left Join (cost=401,877.18..1,044,098.09 rows=3,813,769 width=125) (actual time=2,919.121..21,939.684 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, c.communication_type, c.is_lost, c.is_not_goal, c.person_id, c.communication_number, c.ani, s.id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), (COALESCE(ac.name, lc(ac.lc_name, 74))), (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END)
  • Inner Unique: true
  • Hash Cond: (COALESCE(c.ac_id, '-1'::integer) = ac.id)
  • Buffers: shared hit=3,992,598 read=13,220 dirtied=6, temp read=50,541 written=50,541
12. 5,887.798 20,683.727 ↑ 1.0 3,681,856 1

Hash Left Join (cost=401,457.04..1,033,653.96 rows=3,813,769 width=93) (actual time=2,918.049..20,683.727 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, c.communication_type, c.is_lost, c.is_not_goal, c.person_id, c.communication_number, c.ani, s.id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END)
  • Hash Cond: (c.session_id = s.id)
  • Join Filter: (s.start_time >= (c.start_time - '30 days'::interval))
  • Rows Removed by Join Filter: 2,830
  • Buffers: shared hit=3,992,487 read=13,220 dirtied=6, temp read=50,541 written=50,541
13. 1,074.159 11,888.718 ↑ 1.0 3,681,856 1

Hash Left Join (cost=177.27..412,675.55 rows=3,813,769 width=93) (actual time=1.076..11,888.718 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, c.communication_type, c.is_lost, c.is_not_goal, c.person_id, c.communication_number, c.ani, c.session_id, (CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END), (CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END)
  • Inner Unique: true
  • Hash Cond: (c.event_id = e.id)
  • Buffers: shared hit=3,595,746 read=13,220 dirtied=6
14. 10,813.565 10,813.565 ↑ 1.0 3,681,856 1

Index Scan using fki_communication__app on analytics.communication c (cost=0.56..402,482.77 rows=3,813,769 width=97) (actual time=0.058..10,813.565 rows=3,681,856 loops=1)

  • Output: c.site_id, c.start_time, c.ac_id, c.id, c.communication_type, c.is_lost, c.is_not_goal, c.person_id, c.communication_number, c.ani, c.session_id, c.event_id, CASE WHEN COALESCE(c.is_through_first_good, false) THEN c.id ELSE NULL::integer END, CASE WHEN (c.phone_tracking_type IS NULL) THEN NULL::text WHEN (c.phone_tracking_type = 'static'::analytics.tracking_type_mnemonic) THEN 'Звонки на статический номер'::text WHEN (c.phone_tracking_type = 'dynamic_default'::analytics.tracking_type_mnemonic) THEN 'Звонки на номер по умолчанию'::text ELSE 'Звонки на номера ДТ без сессии'::text END
  • Index Cond: ((c.app_id = 74) AND (c.start_time >= '2019-01-01 00:00:00+03'::timestamp with time zone) AND (c.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Filter: ((c.site_id IS NOT NULL) AND ((c.chat_status <> ALL ('{system,refused}'::consultant.chat_status[])) OR (c.chat_status IS NULL)))
  • Rows Removed by Filter: 993,938
  • Buffers: shared hit=3,595,643 read=13,220 dirtied=6
15. 0.066 0.994 ↓ 1.0 405 1

Hash (cost=171.82..171.82 rows=391 width=4) (actual time=0.994..0.994 rows=405 loops=1)

  • Output: e.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=103
16. 0.928 0.928 ↓ 1.0 405 1

Seq Scan on analytics.event e (cost=0.00..171.82 rows=391 width=4) (actual time=0.018..0.928 rows=405 loops=1)

  • Output: e.id
  • Filter: e.is_goal
  • Rows Removed by Filter: 6,517
  • Buffers: shared hit=103
17. 1,123.336 2,907.211 ↓ 1.0 4,945,015 1

Hash (cost=315,343.30..315,343.30 rows=4,943,718 width=16) (actual time=2,907.211..2,907.211 rows=4,945,015 loops=1)

  • Output: s.id, s.start_time
  • Buckets: 2,097,152 Batches: 8 Memory Usage: 45,366kB
  • Buffers: shared hit=396,741, temp written=19,010
18. 398.190 1,783.875 ↓ 1.0 4,945,015 1

Append (cost=0.00..315,343.30 rows=4,943,718 width=16) (actual time=0.114..1,783.875 rows=4,945,015 loops=1)

  • Buffers: shared hit=396,741
19. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on analytics.session s (cost=0.00..0.00 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: s.id, s.start_time
  • Filter: ((s.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
20. 62.906 62.906 ↑ 1.0 290,331 1

Index Only Scan using idx_session_2019_04__some on partition.session_2019_04 s_1 (cost=0.42..7,572.04 rows=290,331 width=16) (actual time=0.099..62.906 rows=290,331 loops=1)

  • Output: s_1.id, s_1.start_time
  • Index Cond: ((s_1.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_1.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=1,752
21. 51.487 51.487 ↑ 1.0 244,421 1

Index Only Scan using idx_session_2019_05__some on partition.session_2019_05 s_2 (cost=0.42..6,845.84 rows=244,421 width=16) (actual time=0.063..51.487 rows=244,421 loops=1)

  • Output: s_2.id, s_2.start_time
  • Index Cond: ((s_2.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_2.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=1,942
22. 88.612 88.612 ↑ 1.0 375,607 1

Index Only Scan using idx_session_2019_06__some on partition.session_2019_06 s_3 (cost=0.42..17,991.56 rows=375,607 width=16) (actual time=0.083..88.612 rows=375,607 loops=1)

  • Output: s_3.id, s_3.start_time
  • Index Cond: ((s_3.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_3.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=10,371
23. 0.012 0.012 ↓ 0.0 0 1

Index Scan using idx_session_default__start_time on partition.session_default s_4 (cost=0.14..2.16 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: s_4.id, s_4.start_time
  • Index Cond: ((s_4.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_4.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Buffers: shared hit=1
24. 56.075 56.075 ↑ 1.0 260,298 1

Index Only Scan using idx_session_2018_12__some on partition.session_2018_12 s_5 (cost=0.42..6,863.25 rows=260,574 width=16) (actual time=0.130..56.075 rows=260,298 loops=1)

  • Output: s_5.id, s_5.start_time
  • Index Cond: ((s_5.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_5.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=1,598
25. 50.450 50.450 ↑ 1.0 231,919 1

Index Only Scan using idx_session_2019_01__some on partition.session_2019_01 s_6 (cost=0.42..6,049.80 rows=231,919 width=16) (actual time=0.057..50.450 rows=231,919 loops=1)

  • Output: s_6.id, s_6.start_time
  • Index Cond: ((s_6.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_6.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=1,401
26. 56.887 56.887 ↑ 1.0 264,795 1

Index Only Scan using idx_session_2019_02__some on partition.session_2019_02 s_7 (cost=0.42..6,907.32 rows=264,795 width=16) (actual time=0.064..56.887 rows=264,795 loops=1)

  • Output: s_7.id, s_7.start_time
  • Index Cond: ((s_7.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_7.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=1,599
27. 67.953 67.953 ↑ 1.0 307,454 1

Index Only Scan using idx_session_2019_03__some on partition.session_2019_03 s_8 (cost=0.42..8,019.50 rows=307,454 width=16) (actual time=0.065..67.953 rows=307,454 loops=1)

  • Output: s_8.id, s_8.start_time
  • Index Cond: ((s_8.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_8.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=1,856
28. 124.385 124.385 ↑ 1.0 487,235 1

Index Only Scan using idx_session_2019_07__some on partition.session_2019_07 s_9 (cost=0.42..23,591.12 rows=487,235 width=16) (actual time=0.090..124.385 rows=487,235 loops=1)

  • Output: s_9.id, s_9.start_time
  • Index Cond: ((s_9.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_9.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=17,742
29. 115.948 115.948 ↑ 1.0 476,334 1

Index Only Scan using idx_session_2019_08__some on partition.session_2019_08 s_10 (cost=0.42..21,625.10 rows=476,334 width=16) (actual time=0.067..115.948 rows=476,334 loops=1)

  • Output: s_10.id, s_10.start_time
  • Index Cond: ((s_10.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_10.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=15,910
30. 137.093 137.093 ↓ 1.0 476,878 1

Seq Scan on partition.session_2019_09 s_11 (cost=0.00..44,288.18 rows=475,679 width=16) (actual time=0.015..137.093 rows=476,878 loops=1)

  • Output: s_11.id, s_11.start_time
  • Filter: ((s_11.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_11.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Buffers: shared hit=37,153
31. 161.813 161.813 ↑ 1.0 539,731 1

Seq Scan on partition.session_2019_10 s_12 (cost=0.00..51,681.06 rows=539,937 width=16) (actual time=0.012..161.813 rows=539,731 loops=1)

  • Output: s_12.id, s_12.start_time
  • Filter: ((s_12.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_12.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Buffers: shared hit=43,582
32. 262.675 262.675 ↓ 1.0 501,906 1

Index Only Scan using idx_session_2019_11__some on partition.session_2019_11 s_13 (cost=0.42..42,063.96 rows=500,377 width=16) (actual time=0.100..262.675 rows=501,906 loops=1)

  • Output: s_13.id, s_13.start_time
  • Index Cond: ((s_13.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_13.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Heap Fetches: 255,933
  • Buffers: shared hit=222,046
33. 149.377 149.377 ↑ 1.0 488,106 1

Seq Scan on partition.session_2019_12 s_14 (cost=0.00..47,123.79 rows=489,053 width=16) (actual time=0.012..149.377 rows=488,106 loops=1)

  • Output: s_14.id, s_14.start_time
  • Filter: ((s_14.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_14.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Buffers: shared hit=39,788
34. 0.241 1.041 ↑ 1.0 1,048 1

Hash (cost=407.04..407.04 rows=1,048 width=36) (actual time=1.041..1.041 rows=1,048 loops=1)

  • Output: ac.id, (COALESCE(ac.name, lc(ac.lc_name, 74)))
  • Buckets: 2,048 Batches: 1 Memory Usage: 103kB
  • Buffers: shared hit=111
35. 0.731 0.800 ↑ 1.0 1,048 1

Bitmap Heap Scan on analytics.ac (cost=14.94..407.04 rows=1,048 width=36) (actual time=0.125..0.800 rows=1,048 loops=1)

  • Output: ac.id, COALESCE(ac.name, lc(ac.lc_name, 74))
  • Recheck Cond: ((ac.app_id IS NULL) OR (ac.app_id = 74))
  • Heap Blocks: exact=99
  • Buffers: shared hit=111
36. 0.002 0.069 ↓ 0.0 0 1

BitmapOr (cost=14.94..14.94 rows=1,048 width=0) (actual time=0.069..0.069 rows=0 loops=1)

  • Buffers: shared hit=7
37. 0.009 0.009 ↑ 1.0 1 1

Bitmap Index Scan on fki_ac__app (cost=0.00..1.29 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (ac.app_id IS NULL)
  • Buffers: shared hit=2
38. 0.058 0.058 ↑ 1.0 1,047 1

Bitmap Index Scan on fki_ac__app (cost=0.00..13.13 rows=1,047 width=0) (actual time=0.058..0.058 rows=1,047 loops=1)

  • Index Cond: (ac.app_id = 74)
  • Buffers: shared hit=5
39. 2.441 4.618 ↓ 1.0 14,480 1

Hash (cost=262.10..262.10 rows=14,454 width=8) (actual time=4.618..4.618 rows=14,480 loops=1)

  • Output: sl.communication_id, sl.communication_type
  • Buckets: 16,384 Batches: 1 Memory Usage: 694kB
  • Buffers: shared hit=1,456
40. 2.177 2.177 ↓ 1.0 14,480 1

Index Only Scan using fki_sale__communication on analytics.sale sl (cost=0.29..262.10 rows=14,454 width=8) (actual time=0.057..2.177 rows=14,480 loops=1)

  • Output: sl.communication_id, sl.communication_type
  • Heap Fetches: 1,920
  • Buffers: shared hit=1,456
41. 0.000 0.000 ↑ 1.0 1 3,681,856

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=3,681,856)

  • Output: NULL::integer[]
42. 0.000 0.000 ↑ 1.0 1 3,681,856

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=3,681,856)

  • Output: NULL::boolean
43. 0.268 0.490 ↓ 1.0 2,036 1

Hash (cost=38.77..38.77 rows=2,033 width=4) (actual time=0.490..0.490 rows=2,036 loops=1)

  • Output: ss.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=14
44. 0.222 0.222 ↓ 1.0 2,036 1

Index Only Scan using pkey_site on analytics.site ss (cost=0.28..38.77 rows=2,033 width=4) (actual time=0.019..0.222 rows=2,036 loops=1)

  • Output: ss.id
  • Heap Fetches: 31
  • Buffers: shared hit=14
45. 4,909.226 23,086.464 ↓ 1.0 4,943,545 1

Hash (cost=688,808.46..688,808.46 rows=4,942,325 width=144) (actual time=23,086.464..23,086.464 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, (COALESCE(s_15.ppc_place, ''::text)), (lc(device_type.lc_name, 74)), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer))
  • Buckets: 524,288 Batches: 16 Memory Usage: 46,272kB
  • Buffers: shared hit=410,453, temp written=72,514
46. 1,583.461 18,177.238 ↓ 1.0 4,943,545 1

Hash Left Join (cost=17,095.72..688,808.46 rows=4,942,325 width=144) (actual time=193.534..18,177.238 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, (COALESCE(s_15.ppc_place, ''::text)), (lc(device_type.lc_name, 74)), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer))
  • Inner Unique: true
  • Hash Cond: (s_15.site_id = ss_1.id)
  • Buffers: shared hit=410,453
47. 1,060.803 16,593.263 ↓ 1.0 4,943,545 1

Hash Left Join (cost=17,031.53..675,743.62 rows=4,942,325 width=144) (actual time=193.005..16,593.263 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, (COALESCE(s_15.ppc_place, ''::text)), (lc(device_type.lc_name, 74)), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer))
  • Inner Unique: true
  • Hash Cond: (s_15.ppc_region_id = rg.id)
  • Buffers: shared hit=410,439
48. 1,832.428 15,532.191 ↓ 1.0 4,943,545 1

Hash Left Join (cost=16,996.69..662,733.31 rows=4,942,325 width=144) (actual time=192.732..15,532.191 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, (COALESCE(s_15.ppc_place, ''::text)), (lc(device_type.lc_name, 74)), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer))
  • Hash Cond: (s_15.device_type = device_type.id)
  • Buffers: shared hit=410,434
49. 1,932.601 13,699.588 ↓ 1.0 4,943,545 1

Hash Left Join (cost=16,994.60..594,774.25 rows=4,942,325 width=116) (actual time=192.544..13,699.588 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.device_type, (COALESCE(s_15.ppc_place, ''::text)), (COALESCE(ac_1.name, lc(ac_1.lc_name, 74))), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer))
  • Inner Unique: true
  • Hash Cond: (COALESCE(s_15.ac_id, '-1'::integer) = ac_1.id)
  • Buffers: shared hit=410,413
50. 1,152.447 11,766.010 ↓ 1.0 4,943,545 1

Hash Left Join (cost=16,574.46..581,363.85 rows=4,942,325 width=84) (actual time=191.536..11,766.010 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.device_type, (COALESCE(s_15.ppc_place, ''::text)), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), (COALESCE(auditory.id, '-1'::integer))
  • Inner Unique: true
  • Hash Cond: (s_15.ad_engine_id = eng.id)
  • Buffers: shared hit=410,302
51. 1,056.705 10,613.538 ↓ 1.0 4,943,545 1

Hash Left Join (cost=16,573.33..567,098.22 rows=4,942,325 width=88) (actual time=191.489..10,613.538 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.ad_engine_id, s_15.device_type, (COALESCE(s_15.ppc_place, ''::text)), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), (COALESCE(kw.id, '-1'::integer)), COALESCE(auditory.id, '-1'::integer)
  • Inner Unique: true
  • Hash Cond: (s_15.ppc_auditory_id = auditory.id)
  • Buffers: shared hit=410,301
52. 1,078.310 9,556.565 ↓ 1.0 4,943,545 1

Hash Left Join (cost=16,540.17..554,091.34 rows=4,942,325 width=88) (actual time=191.198..9,556.565 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.ppc_auditory_id, s_15.ad_engine_id, s_15.device_type, (COALESCE(s_15.ppc_place, ''::text)), (COALESCE(pc.id, '-1'::integer)), (COALESCE(ad.id, '-1'::integer)), COALESCE(kw.id, '-1'::integer)
  • Inner Unique: true
  • Hash Cond: (s_15.ppc_kw_id = kw.id)
  • Buffers: shared hit=410,296
53. 1,131.705 8,411.076 ↓ 1.0 4,943,545 1

Hash Left Join (cost=11,070.38..535,647.92 rows=4,942,325 width=88) (actual time=122.696..8,411.076 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.ppc_kw_id, s_15.ppc_auditory_id, s_15.ad_engine_id, s_15.device_type, (COALESCE(s_15.ppc_place, ''::text)), (COALESCE(pc.id, '-1'::integer)), COALESCE(ad.id, '-1'::integer)
  • Inner Unique: true
  • Hash Cond: (s_15.ppc_ad_id = ad.id)
  • Buffers: shared hit=395,220
54. 1,093.271 7,172.548 ↓ 1.0 4,943,545 1

Hash Left Join (cost=1,501.44..513,105.38 rows=4,942,325 width=88) (actual time=13.449..7,172.548 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.ppc_ad_id, s_15.ppc_kw_id, s_15.ppc_auditory_id, s_15.ad_engine_id, s_15.device_type, (COALESCE(s_15.ppc_place, ''::text)), (COALESCE(pc.id, '-1'::integer))
  • Inner Unique: true
  • Hash Cond: (s_15.ppc_gr_id = gr.id)
  • Buffers: shared hit=374,654
55. 1,185.569 6,066.689 ↓ 1.0 4,943,545 1

Hash Left Join (cost=82.42..498,712.65 rows=4,942,325 width=92) (actual time=0.560..6,066.689 rows=4,943,545 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.ppc_gr_id, s_15.ppc_ad_id, s_15.ppc_kw_id, s_15.ppc_auditory_id, s_15.ad_engine_id, s_15.device_type, (COALESCE(s_15.ppc_place, ''::text)), COALESCE(pc.id, '-1'::integer)
  • Inner Unique: true
  • Hash Cond: (s_15.ppc_pc_id = pc.id)
  • Buffers: shared hit=374,384
56. 487.411 4,880.606 ↓ 1.0 4,943,545 1

Append (cost=0.00..485,654.18 rows=4,942,325 width=92) (actual time=0.035..4,880.606 rows=4,943,545 loops=1)

  • Buffers: shared hit=374,354
57. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on analytics.session s_15 (cost=0.00..0.00 rows=1 width=92) (actual time=0.012..0.013 rows=0 loops=1)

  • Output: s_15.site_id, s_15.ppc_position_type, s_15.ac_id, s_15.start_time, s_15.ppc_region_id, s_15.id, s_15.ppc_pc_id, s_15.ppc_gr_id, s_15.ppc_ad_id, s_15.ppc_kw_id, s_15.ppc_auditory_id, s_15.ad_engine_id, s_15.device_type, COALESCE(s_15.ppc_place, ''::text)
  • Filter: ((s_15.site_id IS NOT NULL) AND (s_15.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_15.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_15.app_id = 74))
58. 248.602 248.602 ↓ 1.0 290,240 1

Seq Scan on partition.session_2019_04 s_16 (cost=0.00..24,089.79 rows=290,234 width=92) (actual time=0.021..248.602 rows=290,240 loops=1)

  • Output: s_16.site_id, s_16.ppc_position_type, s_16.ac_id, s_16.start_time, s_16.ppc_region_id, s_16.id, s_16.ppc_pc_id, s_16.ppc_gr_id, s_16.ppc_ad_id, s_16.ppc_kw_id, s_16.ppc_auditory_id, s_16.ad_engine_id, s_16.device_type, COALESCE(s_16.ppc_place, ''::text)
  • Filter: ((s_16.site_id IS NOT NULL) AND (s_16.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_16.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_16.app_id = 74))
  • Rows Removed by Filter: 91
  • Buffers: shared hit=19,009
59. 281.972 281.972 ↓ 1.0 244,324 1

Seq Scan on partition.session_2019_05 s_17 (cost=0.00..24,719.37 rows=244,291 width=92) (actual time=0.016..281.972 rows=244,324 loops=1)

  • Output: s_17.site_id, s_17.ppc_position_type, s_17.ac_id, s_17.start_time, s_17.ppc_region_id, s_17.id, s_17.ppc_pc_id, s_17.ppc_gr_id, s_17.ppc_ad_id, s_17.ppc_kw_id, s_17.ppc_auditory_id, s_17.ad_engine_id, s_17.device_type, COALESCE(s_17.ppc_place, ''::text)
  • Filter: ((s_17.site_id IS NOT NULL) AND (s_17.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_17.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_17.app_id = 74))
  • Rows Removed by Filter: 97
  • Buffers: shared hit=20,442
60. 321.030 321.030 ↑ 1.0 375,552 1

Seq Scan on partition.session_2019_06 s_18 (cost=0.00..34,647.12 rows=375,557 width=92) (actual time=0.011..321.030 rows=375,552 loops=1)

  • Output: s_18.site_id, s_18.ppc_position_type, s_18.ac_id, s_18.start_time, s_18.ppc_region_id, s_18.id, s_18.ppc_pc_id, s_18.ppc_gr_id, s_18.ppc_ad_id, s_18.ppc_kw_id, s_18.ppc_auditory_id, s_18.ad_engine_id, s_18.device_type, COALESCE(s_18.ppc_place, ''::text)
  • Filter: ((s_18.site_id IS NOT NULL) AND (s_18.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_18.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_18.app_id = 74))
  • Rows Removed by Filter: 55
  • Buffers: shared hit=28,074
61. 0.033 0.033 ↓ 0.0 0 1

Index Scan using fki_session_default__app on partition.session_default s_19 (cost=0.14..2.16 rows=1 width=92) (actual time=0.033..0.033 rows=0 loops=1)

  • Output: s_19.site_id, s_19.ppc_position_type, s_19.ac_id, s_19.start_time, s_19.ppc_region_id, s_19.id, s_19.ppc_pc_id, s_19.ppc_gr_id, s_19.ppc_ad_id, s_19.ppc_kw_id, s_19.ppc_auditory_id, s_19.ad_engine_id, s_19.device_type, COALESCE(s_19.ppc_place, ''::text)
  • Index Cond: ((s_19.app_id = 74) AND (s_19.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_19.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone))
  • Filter: (s_19.site_id IS NOT NULL)
  • Buffers: shared hit=1
62. 212.232 212.232 ↑ 1.0 260,165 1

Seq Scan on partition.session_2018_12 s_20 (cost=0.00..21,214.41 rows=260,470 width=92) (actual time=0.028..212.232 rows=260,165 loops=1)

  • Output: s_20.site_id, s_20.ppc_position_type, s_20.ac_id, s_20.start_time, s_20.ppc_region_id, s_20.id, s_20.ppc_pc_id, s_20.ppc_gr_id, s_20.ppc_ad_id, s_20.ppc_kw_id, s_20.ppc_auditory_id, s_20.ad_engine_id, s_20.device_type, COALESCE(s_20.ppc_place, ''::text)
  • Filter: ((s_20.site_id IS NOT NULL) AND (s_20.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_20.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_20.app_id = 74))
  • Rows Removed by Filter: 4,544
  • Buffers: shared hit=16,582
63. 181.746 181.746 ↓ 1.0 231,872 1

Seq Scan on partition.session_2019_01 s_21 (cost=0.00..18,711.58 rows=231,842 width=92) (actual time=0.014..181.746 rows=231,872 loops=1)

  • Output: s_21.site_id, s_21.ppc_position_type, s_21.ac_id, s_21.start_time, s_21.ppc_region_id, s_21.id, s_21.ppc_pc_id, s_21.ppc_gr_id, s_21.ppc_ad_id, s_21.ppc_kw_id, s_21.ppc_auditory_id, s_21.ad_engine_id, s_21.device_type, COALESCE(s_21.ppc_place, ''::text)
  • Filter: ((s_21.site_id IS NOT NULL) AND (s_21.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_21.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_21.app_id = 74))
  • Rows Removed by Filter: 47
  • Buffers: shared hit=14,653
64. 216.181 216.181 ↑ 1.0 264,555 1

Seq Scan on partition.session_2019_02 s_22 (cost=0.00..21,386.91 rows=264,636 width=92) (actual time=0.011..216.181 rows=264,555 loops=1)

  • Output: s_22.site_id, s_22.ppc_position_type, s_22.ac_id, s_22.start_time, s_22.ppc_region_id, s_22.id, s_22.ppc_pc_id, s_22.ppc_gr_id, s_22.ppc_ad_id, s_22.ppc_kw_id, s_22.ppc_auditory_id, s_22.ad_engine_id, s_22.device_type, COALESCE(s_22.ppc_place, ''::text)
  • Filter: ((s_22.site_id IS NOT NULL) AND (s_22.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_22.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_22.app_id = 74))
  • Rows Removed by Filter: 240
  • Buffers: shared hit=16,753
65. 257.498 257.498 ↓ 1.0 307,363 1

Seq Scan on partition.session_2019_03 s_23 (cost=0.00..26,471.44 rows=307,352 width=92) (actual time=0.012..257.498 rows=307,363 loops=1)

  • Output: s_23.site_id, s_23.ppc_position_type, s_23.ac_id, s_23.start_time, s_23.ppc_region_id, s_23.id, s_23.ppc_pc_id, s_23.ppc_gr_id, s_23.ppc_ad_id, s_23.ppc_kw_id, s_23.ppc_auditory_id, s_23.ad_engine_id, s_23.device_type, COALESCE(s_23.ppc_place, ''::text)
  • Filter: ((s_23.site_id IS NOT NULL) AND (s_23.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_23.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_23.app_id = 74))
  • Rows Removed by Filter: 91
  • Buffers: shared hit=21,091
66. 466.087 466.087 ↑ 1.0 487,177 1

Seq Scan on partition.session_2019_07 s_24 (cost=0.00..48,098.61 rows=487,203 width=92) (actual time=0.022..466.087 rows=487,177 loops=1)

  • Output: s_24.site_id, s_24.ppc_position_type, s_24.ac_id, s_24.start_time, s_24.ppc_region_id, s_24.id, s_24.ppc_pc_id, s_24.ppc_gr_id, s_24.ppc_ad_id, s_24.ppc_kw_id, s_24.ppc_auditory_id, s_24.ad_engine_id, s_24.device_type, COALESCE(s_24.ppc_place, ''::text)
  • Filter: ((s_24.site_id IS NOT NULL) AND (s_24.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_24.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_24.app_id = 74))
  • Rows Removed by Filter: 58
  • Buffers: shared hit=39,572
67. 438.682 438.682 ↓ 1.0 476,315 1

Seq Scan on partition.session_2019_08 s_25 (cost=0.00..45,966.85 rows=476,302 width=92) (actual time=0.022..438.682 rows=476,315 loops=1)

  • Output: s_25.site_id, s_25.ppc_position_type, s_25.ac_id, s_25.start_time, s_25.ppc_region_id, s_25.id, s_25.ppc_pc_id, s_25.ppc_gr_id, s_25.ppc_ad_id, s_25.ppc_kw_id, s_25.ppc_auditory_id, s_25.ad_engine_id, s_25.device_type, COALESCE(s_25.ppc_place, ''::text)
  • Filter: ((s_25.site_id IS NOT NULL) AND (s_25.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_25.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_25.app_id = 74))
  • Rows Removed by Filter: 19
  • Buffers: shared hit=37,631
68. 381.206 381.206 ↓ 1.0 476,576 1

Seq Scan on partition.session_2019_09 s_26 (cost=0.00..45,477.38 rows=475,378 width=92) (actual time=0.015..381.206 rows=476,576 loops=1)

  • Output: s_26.site_id, s_26.ppc_position_type, s_26.ac_id, s_26.start_time, s_26.ppc_region_id, s_26.id, s_26.ppc_pc_id, s_26.ppc_gr_id, s_26.ppc_ad_id, s_26.ppc_kw_id, s_26.ppc_auditory_id, s_26.ad_engine_id, s_26.device_type, COALESCE(s_26.ppc_place, ''::text)
  • Filter: ((s_26.site_id IS NOT NULL) AND (s_26.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_26.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_26.app_id = 74))
  • Rows Removed by Filter: 302
  • Buffers: shared hit=37,153
69. 512.823 512.823 ↑ 1.0 539,622 1

Seq Scan on partition.session_2019_10 s_27 (cost=0.00..53,030.90 rows=539,793 width=92) (actual time=0.018..512.823 rows=539,622 loops=1)

  • Output: s_27.site_id, s_27.ppc_position_type, s_27.ac_id, s_27.start_time, s_27.ppc_region_id, s_27.id, s_27.ppc_pc_id, s_27.ppc_gr_id, s_27.ppc_ad_id, s_27.ppc_kw_id, s_27.ppc_auditory_id, s_27.ad_engine_id, s_27.device_type, COALESCE(s_27.ppc_place, ''::text)
  • Filter: ((s_27.site_id IS NOT NULL) AND (s_27.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_27.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_27.app_id = 74))
  • Rows Removed by Filter: 109
  • Buffers: shared hit=43,582
70. 431.796 431.796 ↓ 1.0 501,805 1

Seq Scan on partition.session_2019_11 s_28 (cost=0.00..48,779.60 rows=500,310 width=92) (actual time=0.012..431.796 rows=501,805 loops=1)

  • Output: s_28.site_id, s_28.ppc_position_type, s_28.ac_id, s_28.start_time, s_28.ppc_region_id, s_28.id, s_28.ppc_pc_id, s_28.ppc_gr_id, s_28.ppc_ad_id, s_28.ppc_kw_id, s_28.ppc_auditory_id, s_28.ad_engine_id, s_28.device_type, COALESCE(s_28.ppc_place, ''::text)
  • Filter: ((s_28.site_id IS NOT NULL) AND (s_28.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_28.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_28.app_id = 74))
  • Rows Removed by Filter: 101
  • Buffers: shared hit=40,023
71. 443.294 443.294 ↑ 1.0 487,979 1

Seq Scan on partition.session_2019_12 s_29 (cost=0.00..48,346.43 rows=488,955 width=92) (actual time=0.017..443.294 rows=487,979 loops=1)

  • Output: s_29.site_id, s_29.ppc_position_type, s_29.ac_id, s_29.start_time, s_29.ppc_region_id, s_29.id, s_29.ppc_pc_id, s_29.ppc_gr_id, s_29.ppc_ad_id, s_29.ppc_kw_id, s_29.ppc_auditory_id, s_29.ad_engine_id, s_29.device_type, COALESCE(s_29.ppc_place, ''::text)
  • Filter: ((s_29.site_id IS NOT NULL) AND (s_29.start_time >= '2018-12-02 00:00:00+03'::timestamp with time zone) AND (s_29.start_time <= '2019-12-31 23:59:59+03'::timestamp with time zone) AND (s_29.app_id = 74))
  • Rows Removed by Filter: 127
  • Buffers: shared hit=39,788
72. 0.239 0.514 ↑ 1.0 1,896 1

Hash (cost=58.72..58.72 rows=1,896 width=4) (actual time=0.514..0.514 rows=1,896 loops=1)

  • Output: pc.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 83kB
  • Buffers: shared hit=30
73. 0.275 0.275 ↑ 1.0 1,896 1

Index Only Scan using uni_pc__id_engine on ppc.pc (cost=0.28..58.72 rows=1,896 width=4) (actual time=0.026..0.275 rows=1,896 loops=1)

  • Output: pc.id
  • Heap Fetches: 0
  • Buffers: shared hit=30
74. 7.433 12.588 ↓ 1.1 49,003 1

Hash (cost=844.60..844.60 rows=45,954 width=4) (actual time=12.588..12.588 rows=49,003 loops=1)

  • Output: gr.id
  • Buckets: 65,536 Batches: 1 Memory Usage: 2,235kB
  • Buffers: shared hit=270
75. 5.155 5.155 ↓ 1.1 49,003 1

Index Only Scan using pkey_gr on ppc.gr (cost=0.29..844.60 rows=45,954 width=4) (actual time=0.020..5.155 rows=49,003 loops=1)

  • Output: gr.id
  • Heap Fetches: 1,194
  • Buffers: shared hit=270
76. 57.651 106.823 ↓ 1.1 324,217 1

Hash (cost=5,806.88..5,806.88 rows=300,964 width=4) (actual time=106.823..106.823 rows=324,217 loops=1)

  • Output: ad.id
  • Buckets: 524,288 Batches: 1 Memory Usage: 15,495kB
  • Buffers: shared hit=20,566
77. 49.172 49.172 ↓ 1.1 324,217 1

Index Only Scan using pkey_ad on ppc.ad (cost=0.42..5,806.88 rows=300,964 width=4) (actual time=0.025..49.172 rows=324,217 loops=1)

  • Output: ad.id
  • Heap Fetches: 51,079
  • Buffers: shared hit=20,566
78. 35.732 67.179 ↓ 1.2 213,088 1

Hash (cost=3,306.44..3,306.44 rows=173,068 width=4) (actual time=67.179..67.179 rows=213,088 loops=1)

  • Output: kw.id
  • Buckets: 262,144 Batches: 1 Memory Usage: 9,540kB
  • Buffers: shared hit=15,076
79. 31.447 31.447 ↓ 1.2 213,088 1

Index Only Scan using pkey_kw on ppc.kw (cost=0.42..3,306.44 rows=173,068 width=4) (actual time=0.027..31.447 rows=213,088 loops=1)

  • Output: kw.id
  • Heap Fetches: 37,631
  • Buffers: shared hit=15,076
80. 0.141 0.268 ↑ 1.0 1,014 1

Hash (cost=20.48..20.48 rows=1,014 width=4) (actual time=0.268..0.268 rows=1,014 loops=1)

  • Output: auditory.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
  • Buffers: shared hit=5
81. 0.127 0.127 ↑ 1.0 1,014 1

Index Only Scan using pkey_auditory on ppc.auditory (cost=0.28..20.48 rows=1,014 width=4) (actual time=0.033..0.127 rows=1,014 loops=1)

  • Output: auditory.id
  • Heap Fetches: 0
  • Buffers: shared hit=5
82. 0.014 0.025 ↓ 1.3 8 1

Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.025..0.025 rows=8 loops=1)

  • Output: eng.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
83. 0.011 0.011 ↓ 1.3 8 1

Seq Scan on analytics.ad_engine eng (cost=0.00..1.06 rows=6 width=4) (actual time=0.010..0.011 rows=8 loops=1)

  • Output: eng.id
  • Buffers: shared hit=1
84. 0.248 0.977 ↑ 1.0 1,048 1

Hash (cost=407.04..407.04 rows=1,048 width=36) (actual time=0.977..0.977 rows=1,048 loops=1)

  • Output: ac_1.id, (COALESCE(ac_1.name, lc(ac_1.lc_name, 74)))
  • Buckets: 2,048 Batches: 1 Memory Usage: 103kB
  • Buffers: shared hit=111
85. 0.662 0.729 ↑ 1.0 1,048 1

Bitmap Heap Scan on analytics.ac ac_1 (cost=14.94..407.04 rows=1,048 width=36) (actual time=0.083..0.729 rows=1,048 loops=1)

  • Output: ac_1.id, COALESCE(ac_1.name, lc(ac_1.lc_name, 74))
  • Recheck Cond: ((ac_1.app_id IS NULL) OR (ac_1.app_id = 74))
  • Heap Blocks: exact=99
  • Buffers: shared hit=111
86. 0.001 0.067 ↓ 0.0 0 1

BitmapOr (cost=14.94..14.94 rows=1,048 width=0) (actual time=0.067..0.067 rows=0 loops=1)

  • Buffers: shared hit=7
87. 0.009 0.009 ↑ 1.0 1 1

Bitmap Index Scan on fki_ac__app (cost=0.00..1.29 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (ac_1.app_id IS NULL)
  • Buffers: shared hit=2
88. 0.057 0.057 ↑ 1.0 1,047 1

Bitmap Index Scan on fki_ac__app (cost=0.00..13.13 rows=1,047 width=0) (actual time=0.057..0.057 rows=1,047 loops=1)

  • Index Cond: (ac_1.app_id = 74)
  • Buffers: shared hit=5
89. 0.007 0.175 ↑ 1.0 4 1

Hash (cost=2.04..2.04 rows=4 width=36) (actual time=0.175..0.175 rows=4 loops=1)

  • Output: device_type.id, (lc(device_type.lc_name, 74))
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=21
90. 0.168 0.168 ↑ 1.0 4 1

Seq Scan on public.device_type (cost=0.00..2.04 rows=4 width=36) (actual time=0.074..0.168 rows=4 loops=1)

  • Output: device_type.id, lc(device_type.lc_name, 74)
  • Buffers: shared hit=21
91. 0.151 0.269 ↑ 1.0 1,075 1

Hash (cost=21.40..21.40 rows=1,075 width=4) (actual time=0.268..0.269 rows=1,075 loops=1)

  • Output: rg.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 54kB
  • Buffers: shared hit=5
92. 0.118 0.118 ↑ 1.0 1,075 1

Index Only Scan using pkey_region on ppc.region rg (cost=0.28..21.40 rows=1,075 width=4) (actual time=0.019..0.118 rows=1,075 loops=1)

  • Output: rg.id
  • Heap Fetches: 0
  • Buffers: shared hit=5
93. 0.275 0.514 ↓ 1.0 2,036 1

Hash (cost=38.77..38.77 rows=2,033 width=4) (actual time=0.514..0.514 rows=2,036 loops=1)

  • Output: ss_1.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=14
94. 0.239 0.239 ↓ 1.0 2,036 1

Index Only Scan using pkey_site on analytics.site ss_1 (cost=0.28..38.77 rows=2,033 width=4) (actual time=0.023..0.239 rows=2,036 loops=1)

  • Output: ss_1.id
  • Heap Fetches: 31
  • Buffers: shared hit=14
95. 214.928 852.338 ↓ 41.7 378,090 1

Hash (cost=22,841.22..22,841.22 rows=9,057 width=160) (actual time=852.338..852.338 rows=378,090 loops=1)

  • Output: "*SELECT* 1".ac_name, "*SELECT* 1".site_id, "*SELECT* 1".ac_id, "*SELECT* 1".pc_id, "*SELECT* 1".day, "*SELECT* 1".ad_id, "*SELECT* 1".kw_id, "*SELECT* 1".auditory_id, "*SELECT* 1".place, "*SELECT* 1".position_type, "*SELECT* 1".device_type, "*SELECT* 1".region_id
  • Buckets: 524,288 (originally 16384) Batches: 1 (originally 1) Memory Usage: 46,276kB
  • Buffers: shared hit=7,717
96. 33.867 637.410 ↓ 41.7 378,090 1

Append (cost=212.14..22,841.22 rows=9,057 width=160) (actual time=0.852..637.410 rows=378,090 loops=1)

  • Buffers: shared hit=7,717
97. 61.115 601.836 ↓ 42.5 377,564 1

Subquery Scan on *SELECT* 1 (cost=212.14..22,571.39 rows=8,893 width=160) (actual time=0.851..601.836 rows=377,564 loops=1)

  • Output: "*SELECT* 1".ac_name, "*SELECT* 1".site_id, "*SELECT* 1".ac_id, "*SELECT* 1".pc_id, "*SELECT* 1".day, "*SELECT* 1".ad_id, "*SELECT* 1".kw_id, "*SELECT* 1".auditory_id, "*SELECT* 1".place, "*SELECT* 1".position_type, "*SELECT* 1".device_type, "*SELECT* 1".region_id
  • Buffers: shared hit=7,568
98. 164.738 540.721 ↓ 42.5 377,564 1

Hash Left Join (cost=212.14..22,482.46 rows=8,893 width=624) (actual time=0.851..540.721 rows=377,564 loops=1)

  • Output: st.site_id, NULL::text, st.day, st.ac_id, COALESCE(ac_2.name, lc(ac_2.lc_name, 74)), NULL::ppc.cost_ratio_operator_mnemonic, NULL::numeric(12,4), st.pc_id, NULL::text, NULL::integer, NULL::text, st.ad_id, NULL::text, '-1'::integer, NULL::text, '-1'::integer, NULL::text, ''::text, NULL::text, ''::text, '-1'::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::numeric, pc_1.id
  • Inner Unique: true
  • Hash Cond: (st.pc_id = pc_1.id)
  • Buffers: shared hit=7,568
99. 175.280 375.442 ↓ 42.5 377,564 1

Hash Join (cost=129.72..20,153.39 rows=8,893 width=66) (actual time=0.290..375.442 rows=377,564 loops=1)

  • Output: st.site_id, st.day, st.ac_id, st.pc_id, st.ad_id, ac_2.name, ac_2.lc_name
  • Inner Unique: true
  • Hash Cond: (st.ac_id = ac_2.id)
  • Join Filter: (((ac_2.engine = ANY ('{google.adwords,bing.ads}'::ppc.engine_mnemonic[])) AND (st.viewpoint = 'full'::ppc.viewpoint_mnemonic)) OR (ac_2.engine <> ALL ('{google.adwords,bing.ads}'::ppc.engine_mnemonic[])))
  • Rows Removed by Join Filter: 120,507
  • Buffers: shared hit=7,538
100. 43.136 199.904 ↑ 1.0 498,071 1

Append (cost=0.00..18,716.02 rows=498,090 width=28) (actual time=0.013..199.904 rows=498,071 loops=1)

  • Buffers: shared hit=7,485
101. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on data_mart.ppc_ac_pc_gr_ad_stat st (cost=0.00..0.00 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: st.site_id, st.day, st.ac_id, st.pc_id, st.ad_id, st.gr_id, st.viewpoint
  • Filter: ((st.day >= '2019-01-01'::date) AND (st.day <= '2019-12-31'::date) AND (st.app_id = 74))
102. 7.522 7.522 ↓ 1.0 22,768 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_01 st_1 (cost=0.00..756.42 rows=22,710 width=28) (actual time=0.007..7.522 rows=22,768 loops=1)

  • Output: st_1.site_id, st_1.day, st_1.ac_id, st_1.pc_id, st_1.ad_id, st_1.gr_id, st_1.viewpoint
  • Filter: ((st_1.day >= '2019-01-01'::date) AND (st_1.day <= '2019-12-31'::date) AND (st_1.app_id = 74))
  • Buffers: shared hit=359
103. 0.015 0.015 ↓ 0.0 0 1

Index Scan using idx_ppc_ac_pc_gr_ad_stat_default__pc_id__day on partition.ppc_ac_pc_gr_ad_stat_default st_2 (cost=0.15..6.37 rows=1 width=28) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: st_2.site_id, st_2.day, st_2.ac_id, st_2.pc_id, st_2.ad_id, st_2.gr_id, st_2.viewpoint
  • Index Cond: ((st_2.day >= '2019-01-01'::date) AND (st_2.day <= '2019-12-31'::date))
  • Filter: (st_2.app_id = 74)
  • Buffers: shared hit=1
104. 10.789 10.789 ↓ 1.0 34,300 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_02 st_3 (cost=0.00..1,134.30 rows=34,294 width=28) (actual time=0.007..10.789 rows=34,300 loops=1)

  • Output: st_3.site_id, st_3.day, st_3.ac_id, st_3.pc_id, st_3.ad_id, st_3.gr_id, st_3.viewpoint
  • Filter: ((st_3.day >= '2019-01-01'::date) AND (st_3.day <= '2019-12-31'::date) AND (st_3.app_id = 74))
  • Rows Removed by Filter: 8
  • Buffers: shared hit=534
105. 11.652 11.652 ↑ 1.0 35,973 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_03 st_4 (cost=0.00..1,176.53 rows=35,973 width=28) (actual time=0.009..11.652 rows=35,973 loops=1)

  • Output: st_4.site_id, st_4.day, st_4.ac_id, st_4.pc_id, st_4.ad_id, st_4.gr_id, st_4.viewpoint
  • Filter: ((st_4.day >= '2019-01-01'::date) AND (st_4.day <= '2019-12-31'::date) AND (st_4.app_id = 74))
  • Buffers: shared hit=547
106. 11.699 11.699 ↑ 1.0 37,780 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_04 st_5 (cost=0.00..1,263.04 rows=37,878 width=28) (actual time=0.008..11.699 rows=37,780 loops=1)

  • Output: st_5.site_id, st_5.day, st_5.ac_id, st_5.pc_id, st_5.ad_id, st_5.gr_id, st_5.viewpoint
  • Filter: ((st_5.day >= '2019-01-01'::date) AND (st_5.day <= '2019-12-31'::date) AND (st_5.app_id = 74))
  • Rows Removed by Filter: 14
  • Buffers: shared hit=600
107. 9.424 9.424 ↑ 1.0 30,830 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_05 st_6 (cost=0.00..1,023.65 rows=30,830 width=28) (actual time=0.009..9.424 rows=30,830 loops=1)

  • Output: st_6.site_id, st_6.day, st_6.ac_id, st_6.pc_id, st_6.ad_id, st_6.gr_id, st_6.viewpoint
  • Filter: ((st_6.day >= '2019-01-01'::date) AND (st_6.day <= '2019-12-31'::date) AND (st_6.app_id = 74))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=484
108. 11.523 11.523 ↑ 1.0 37,168 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_06 st_7 (cost=0.00..1,220.47 rows=37,168 width=28) (actual time=0.011..11.523 rows=37,168 loops=1)

  • Output: st_7.site_id, st_7.day, st_7.ac_id, st_7.pc_id, st_7.ad_id, st_7.gr_id, st_7.viewpoint
  • Filter: ((st_7.day >= '2019-01-01'::date) AND (st_7.day <= '2019-12-31'::date) AND (st_7.app_id = 74))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=570
109. 14.804 14.804 ↑ 1.0 47,267 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_07 st_8 (cost=0.00..1,545.17 rows=47,267 width=28) (actual time=0.008..14.804 rows=47,267 loops=1)

  • Output: st_8.site_id, st_8.day, st_8.ac_id, st_8.pc_id, st_8.ad_id, st_8.gr_id, st_8.viewpoint
  • Filter: ((st_8.day >= '2019-01-01'::date) AND (st_8.day <= '2019-12-31'::date) AND (st_8.app_id = 74))
  • Buffers: shared hit=718
110. 14.006 14.006 ↑ 1.0 45,791 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_08 st_9 (cost=0.00..1,454.34 rows=45,791 width=28) (actual time=0.010..14.006 rows=45,791 loops=1)

  • Output: st_9.site_id, st_9.day, st_9.ac_id, st_9.pc_id, st_9.ad_id, st_9.gr_id, st_9.viewpoint
  • Filter: ((st_9.day >= '2019-01-01'::date) AND (st_9.day <= '2019-12-31'::date) AND (st_9.app_id = 74))
  • Buffers: shared hit=653
111. 12.814 12.814 ↑ 1.0 42,665 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_09 st_10 (cost=0.00..1,365.81 rows=42,668 width=28) (actual time=0.010..12.814 rows=42,665 loops=1)

  • Output: st_10.site_id, st_10.day, st_10.ac_id, st_10.pc_id, st_10.ad_id, st_10.gr_id, st_10.viewpoint
  • Filter: ((st_10.day >= '2019-01-01'::date) AND (st_10.day <= '2019-12-31'::date) AND (st_10.app_id = 74))
  • Rows Removed by Filter: 10
  • Buffers: shared hit=619
112. 16.576 16.576 ↓ 1.0 53,356 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_10 st_11 (cost=0.00..1,736.66 rows=53,341 width=28) (actual time=0.012..16.576 rows=53,356 loops=1)

  • Output: st_11.site_id, st_11.day, st_11.ac_id, st_11.pc_id, st_11.ad_id, st_11.gr_id, st_11.viewpoint
  • Filter: ((st_11.day >= '2019-01-01'::date) AND (st_11.day <= '2019-12-31'::date) AND (st_11.app_id = 74))
  • Rows Removed by Filter: 13
  • Buffers: shared hit=803
113. 19.867 19.867 ↑ 1.0 56,079 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_11 st_12 (cost=0.00..1,778.42 rows=56,079 width=28) (actual time=0.010..19.867 rows=56,079 loops=1)

  • Output: st_12.site_id, st_12.day, st_12.ac_id, st_12.pc_id, st_12.ad_id, st_12.gr_id, st_12.viewpoint
  • Filter: ((st_12.day >= '2019-01-01'::date) AND (st_12.day <= '2019-12-31'::date) AND (st_12.app_id = 74))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=797
114. 16.072 16.072 ↓ 1.0 54,094 1

Seq Scan on partition.ppc_ac_pc_gr_ad_stat_2019_12 st_13 (cost=0.00..1,764.37 rows=54,089 width=28) (actual time=0.020..16.072 rows=54,094 loops=1)

  • Output: st_13.site_id, st_13.day, st_13.ac_id, st_13.pc_id, st_13.ad_id, st_13.gr_id, st_13.viewpoint
  • Filter: ((st_13.day >= '2019-01-01'::date) AND (st_13.day <= '2019-12-31'::date) AND (st_13.app_id = 74))
  • Rows Removed by Filter: 1,013
  • Buffers: shared hit=800
115. 0.024 0.258 ↓ 1.1 78 1

Hash (cost=128.85..128.85 rows=70 width=54) (actual time=0.257..0.258 rows=78 loops=1)

  • Output: ac_2.name, ac_2.lc_name, ac_2.id, ac_2.engine
  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=53
116. 0.102 0.234 ↓ 1.1 78 1

Bitmap Heap Scan on analytics.ac ac_2 (cost=24.77..128.85 rows=70 width=54) (actual time=0.142..0.234 rows=78 loops=1)

  • Output: ac_2.name, ac_2.lc_name, ac_2.id, ac_2.engine
  • Recheck Cond: ((ac_2.engine IS NOT NULL) AND ((ac_2.app_id IS NULL) OR (ac_2.app_id = 74)))
  • Filter: ((ac_2.engine = ANY ('{google.adwords,bing.ads}'::ppc.engine_mnemonic[])) OR (ac_2.engine <> ALL ('{google.adwords,bing.ads}'::ppc.engine_mnemonic[])))
  • Heap Blocks: exact=40
  • Buffers: shared hit=53
117. 0.006 0.132 ↓ 0.0 0 1

BitmapAnd (cost=24.77..24.77 rows=176 width=0) (actual time=0.132..0.132 rows=0 loops=1)

  • Buffers: shared hit=13
118. 0.062 0.062 ↓ 1.1 615 1

Bitmap Index Scan on fki_ac__engine_site (cost=0.00..10.04 rows=554 width=0) (actual time=0.062..0.062 rows=615 loops=1)

  • Buffers: shared hit=6
119. 0.001 0.064 ↓ 0.0 0 1

BitmapOr (cost=14.45..14.45 rows=1,048 width=0) (actual time=0.064..0.064 rows=0 loops=1)

  • Buffers: shared hit=7
120. 0.004 0.004 ↑ 1.0 1 1

Bitmap Index Scan on fki_ac__app (cost=0.00..1.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (ac_2.app_id IS NULL)
  • Buffers: shared hit=2
121. 0.059 0.059 ↑ 1.0 1,047 1

Bitmap Index Scan on fki_ac__app (cost=0.00..13.13 rows=1,047 width=0) (actual time=0.059..0.059 rows=1,047 loops=1)

  • Index Cond: (ac_2.app_id = 74)
  • Buffers: shared hit=5
122. 0.279 0.541 ↑ 1.0 1,896 1

Hash (cost=58.72..58.72 rows=1,896 width=4) (actual time=0.541..0.541 rows=1,896 loops=1)

  • Output: pc_1.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 83kB
  • Buffers: shared hit=30
123. 0.262 0.262 ↑ 1.0 1,896 1

Index Only Scan using uni_pc__id_engine on ppc.pc pc_1 (cost=0.28..58.72 rows=1,896 width=4) (actual time=0.023..0.262 rows=1,896 loops=1)

  • Output: pc_1.id
  • Heap Fetches: 0
  • Buffers: shared hit=30
124. 0.086 1.707 ↓ 3.2 526 1

Subquery Scan on *SELECT* 2 (cost=158.14..224.55 rows=164 width=160) (actual time=0.878..1.707 rows=526 loops=1)

  • Output: "*SELECT* 2".ac_name, "*SELECT* 2".site_id, "*SELECT* 2".ac_id, "*SELECT* 2".pc_id, "*SELECT* 2".day, "*SELECT* 2".ad_id, "*SELECT* 2".kw_id, "*SELECT* 2".auditory_id, "*SELECT* 2".place, "*SELECT* 2".position_type, "*SELECT* 2".device_type, "*SELECT* 2".region_id
  • Buffers: shared hit=149
125. 0.629 1.621 ↓ 3.2 526 1

Hash Join (cost=158.14..222.91 rows=164 width=620) (actual time=0.877..1.621 rows=526 loops=1)

  • Output: mc.site_id, NULL::text, mc.day, mc.ac_id, COALESCE(ac_3.name, lc(ac_3.lc_name, 74)), NULL::ppc.cost_ratio_operator_mnemonic, NULL::numeric(12,4), NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::numeric
  • Inner Unique: true
  • Hash Cond: (mc.ac_id = ac_3.id)
  • Buffers: shared hit=149
126. 0.152 0.152 ↓ 1.0 526 1

Seq Scan on analytics.cost mc (cost=0.00..22.40 rows=518 width=12) (actual time=0.012..0.152 rows=526 loops=1)

  • Output: mc.app_id, mc.site_id, mc.ac_id, mc.day, mc.cost
  • Filter: ((mc.day >= '2019-01-01'::date) AND (mc.day <= '2019-12-31'::date) AND (mc.app_id = 74))
  • Rows Removed by Filter: 331
  • Buffers: shared hit=8
127. 0.284 0.840 ↑ 1.0 1,048 1

Hash (cost=145.04..145.04 rows=1,048 width=50) (actual time=0.840..0.840 rows=1,048 loops=1)

  • Output: ac_3.name, ac_3.lc_name, ac_3.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 104kB
  • Buffers: shared hit=106
128. 0.485 0.556 ↑ 1.0 1,048 1

Bitmap Heap Scan on analytics.ac ac_3 (cost=14.94..145.04 rows=1,048 width=50) (actual time=0.086..0.556 rows=1,048 loops=1)

  • Output: ac_3.name, ac_3.lc_name, ac_3.id
  • Recheck Cond: ((ac_3.app_id IS NULL) OR (ac_3.app_id = 74))
  • Heap Blocks: exact=99
  • Buffers: shared hit=106
129. 0.004 0.071 ↓ 0.0 0 1

BitmapOr (cost=14.94..14.94 rows=1,048 width=0) (actual time=0.071..0.071 rows=0 loops=1)

  • Buffers: shared hit=7
130. 0.009 0.009 ↑ 1.0 1 1

Bitmap Index Scan on fki_ac__app (cost=0.00..1.29 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (ac_3.app_id IS NULL)
  • Buffers: shared hit=2
131. 0.058 0.058 ↑ 1.0 1,047 1

Bitmap Index Scan on fki_ac__app (cost=0.00..13.13 rows=1,047 width=0) (actual time=0.058..0.058 rows=1,047 loops=1)

  • Index Cond: (ac_3.app_id = 74)
  • Buffers: shared hit=5
132.          

CTE w_no_grouping_sets

133. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=84) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: NULL::integer, NULL::text, NULL::timestamp without time zone, NULL::bigint, NULL::numeric
134. 0.004 136,636.335 ↑ 200.0 1 1

Merge Left Join (cost=1,028,511.31..1,091,106.76 rows=200 width=100) (actual time=136,636.335..136,636.335 rows=1 loops=1)

  • Output: r.id, (json_agg(json_build_object('dimension', n.dimension, 'on_date', n.on_date, 'metric', n.metric) ORDER BY n.priority) FILTER (WHERE ((n.dimension IS NOT NULL) OR (n.on_date IS NOT NULL) OR (n.metric <> '0'::numeric)))), (json_agg(json_build_object('dimension', (to_jsonb(r.*) -> ('dimension'::text || (r.id)::text)), 'on_date', (to_jsonb(r.*) -> ('on_date'::text || (r.id)::text)), 'metric', (to_jsonb(r.*) -> ('metric'::text || (r.id)::text))) ORDER BY (to_jsonb(r.*) -> ('priority'::text || (r.id)::text))) FILTER (WHERE ((NOT r.is_total) AND (((to_jsonb(r.*) ->> ('dimension'::text || (r.id)::text)) IS NOT NULL) OR ((to_jsonb(r.*) ->> ('on_date'::text || (r.id)::text)) IS NOT NULL) OR ((to_jsonb(r.*) ->> ('metric'::text || (r.id)::text)) <> '0'::text))))), (array_agg(json_build_object('total_metric', (to_jsonb(r.*) -> ('metric'::text || (r.id)::text)))) FILTER (WHERE r.is_total))
  • Inner Unique: true
  • Merge Cond: (r.id = n.id)
  • Buffers: shared hit=4,412,260 read=13,220 dirtied=6, temp read=274,182 written=328,813
135. 0.409 136,636.276 ↑ 200.0 1 1

GroupAggregate (cost=1,028,511.28..1,091,104.18 rows=200 width=68) (actual time=136,636.276..136,636.276 rows=1 loops=1)

  • Output: r.id, json_agg(json_build_object('dimension', (to_jsonb(r.*) -> ('dimension'::text || (r.id)::text)), 'on_date', (to_jsonb(r.*) -> ('on_date'::text || (r.id)::text)), 'metric', (to_jsonb(r.*) -> ('metric'::text || (r.id)::text))) ORDER BY (to_jsonb(r.*) -> ('priority'::text || (r.id)::text))) FILTER (WHERE ((NOT r.is_total) AND (((to_jsonb(r.*) ->> ('dimension'::text || (r.id)::text)) IS NOT NULL) OR ((to_jsonb(r.*) ->> ('on_date'::text || (r.id)::text)) IS NOT NULL) OR ((to_jsonb(r.*) ->> ('metric'::text || (r.id)::text)) <> '0'::text)))), array_agg(json_build_object('total_metric', (to_jsonb(r.*) -> ('metric'::text || (r.id)::text)))) FILTER (WHERE r.is_total)
  • Group Key: r.id
  • Buffers: shared hit=4,412,260 read=13,220 dirtied=6, temp read=274,182 written=328,813
136. 0.055 136,635.867 ↑ 48,425.5 11 1

Sort (cost=1,028,511.28..1,029,842.98 rows=532,680 width=46) (actual time=136,635.865..136,635.867 rows=11 loops=1)

  • Output: r.id, r.*, r.is_total
  • Sort Key: r.id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=4,412,252 read=13,220 dirtied=6, temp read=274,182 written=328,813
137. 0.017 136,635.812 ↑ 48,425.5 11 1

Subquery Scan on r (cost=959,201.86..977,845.66 rows=532,680 width=46) (actual time=136,635.796..136,635.812 rows=11 loops=1)

  • Output: r.id, r.*, r.is_total
  • Buffers: shared hit=4,412,252 read=13,220 dirtied=6, temp read=274,182 written=328,813
138. 0.026 136,635.795 ↑ 48,425.5 11 1

WindowAgg (cost=959,201.86..972,518.86 rows=532,680 width=97) (actual time=136,635.783..136,635.795 rows=11 loops=1)

  • Output: r_1.id, r_1.is_total, r_1.id12765, r_1.dimension12765[1], r_1.on_date12765, row_number() OVER (?), (COALESCE(r_1.metric12765, '0'::numeric)), r_1.dim_priority12765
  • Buffers: shared hit=4,412,252 read=13,220 dirtied=6, temp read=274,182 written=328,813
139. 0.034 136,635.769 ↑ 48,425.5 11 1

Sort (cost=959,201.86..960,533.56 rows=532,680 width=89) (actual time=136,635.768..136,635.769 rows=11 loops=1)

  • Output: r_1.id12765, r_1.on_date12765, (COALESCE(r_1.metric12765, '0'::numeric)), r_1.dim_priority12765, r_1.id, r_1.is_total, r_1.dimension12765
  • Sort Key: r_1.id12765, r_1.on_date12765, r_1.dim_priority12765, (COALESCE(r_1.metric12765, '0'::numeric)) DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=4,412,252 read=13,220 dirtied=6, temp read=274,182 written=328,813
140. 0.004 136,635.735 ↑ 48,425.5 11 1

Subquery Scan on r_1 (cost=801,206.90..908,536.25 rows=532,680 width=89) (actual time=136,635.632..136,635.735 rows=11 loops=1)

  • Output: r_1.id12765, r_1.on_date12765, COALESCE(r_1.metric12765, '0'::numeric), r_1.dim_priority12765, r_1.id, r_1.is_total, r_1.dimension12765
  • Buffers: shared hit=4,412,252 read=13,220 dirtied=6, temp read=274,182 written=328,813
141. 0.101 136,635.731 ↑ 48,425.5 11 1

GroupAggregate (cost=801,206.90..903,209.45 rows=532,680 width=89) (actual time=136,635.631..136,635.731 rows=11 loops=1)

  • Output: r_2.id, r_2.is_total, r_2.id12765, r_2.dimension12765, r_2.on_date12765, sum(r_2.metric12765), min(r_2.dim_priority12765)
  • Group Key: r_2.id, r_2.is_total, r_2.id12765, r_2.dimension12765, r_2.on_date12765
  • Filter: (r_2.is_total OR ((r_2.id12765 IS NOT NULL) AND (min(r_2.dim_priority12765) < '11'::bigint)))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=4,412,252 read=13,220 dirtied=6, temp read=274,182 written=328,813
142. 0.227 136,635.630 ↑ 20,725.9 193 1

Sort (cost=801,206.90..811,207.15 rows=4,000,100 width=65) (actual time=136,635.621..136,635.630 rows=193 loops=1)

  • Output: r_2.id, r_2.is_total, r_2.id12765, r_2.dimension12765, r_2.on_date12765, r_2.metric12765, r_2.dim_priority12765
  • Sort Key: r_2.id, r_2.is_total, r_2.id12765, r_2.dimension12765, r_2.on_date12765
  • Sort Method: quicksort Memory: 52kB
  • Buffers: shared hit=4,412,252 read=13,220 dirtied=6, temp read=274,182 written=328,813
143. 0.030 136,635.403 ↑ 20,725.9 193 1

Subquery Scan on r_2 (cost=207,208.30..268,809.84 rows=4,000,100 width=65) (actual time=136,634.983..136,635.403 rows=193 loops=1)

  • Output: r_2.id, r_2.is_total, r_2.id12765, r_2.dimension12765, r_2.on_date12765, r_2.metric12765, r_2.dim_priority12765
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
144. 0.187 136,635.373 ↑ 20,725.9 193 1

ProjectSet (cost=207,208.30..228,808.84 rows=4,000,100 width=161) (actual time=136,634.982..136,635.373 rows=193 loops=1)

  • Output: unnest(CASE WHEN r_3.is_total THEN '{12765}'::integer[] ELSE ARRAY[r_3.id] END), r_3.is_total, CASE WHEN (r_3.id12765 IS NULL) THEN '{NULL,NULL}'::text[] ELSE CASE WHEN (((dense_rank() OVER (?)) < '11'::bigint) OR (((dense_rank() OVER (?)) = '11'::bigint) AND (r_3.cnt_dim_values12765 = '11'::bigint))) THEN r_3.dimension12765 WHEN (r_3.dimension12765 IS NOT NULL) THEN '{"Остальные кампании",NULL}'::text[] ELSE NULL::text[] END END, (dense_rank() OVER (?)), r_3.id12765, NULL::timestamp without time zone, r_3.metric12765, r_3.dim_total_metric12765, (r_3.dimension12765[2]), (r_3.dimension12765[1])
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
145. 0.216 136,635.186 ↑ 207.3 193 1

WindowAgg (cost=207,208.30..208,208.32 rows=40,001 width=161) (actual time=136,634.964..136,635.186 rows=193 loops=1)

  • Output: r_3.is_total, r_3.id, r_3.id12765, dense_rank() OVER (?), r_3.cnt_dim_values12765, r_3.dimension12765, r_3.metric12765, r_3.dim_total_metric12765, (r_3.dimension12765[2]), (r_3.dimension12765[1])
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
146. 0.364 136,634.970 ↑ 207.3 193 1

Sort (cost=207,208.30..207,308.30 rows=40,001 width=153) (actual time=136,634.955..136,634.970 rows=193 loops=1)

  • Output: r_3.id12765, r_3.dim_total_metric12765, (r_3.dimension12765[2]), (r_3.dimension12765[1]), r_3.is_total, r_3.id, r_3.cnt_dim_values12765, r_3.dimension12765, r_3.metric12765
  • Sort Key: r_3.id12765, (r_3.dimension12765[2]), r_3.dim_total_metric12765 DESC NULLS LAST, (r_3.dimension12765[1])
  • Sort Method: quicksort Memory: 76kB
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
147. 0.052 136,634.606 ↑ 207.3 193 1

Subquery Scan on r_3 (cost=201,150.60..204,150.67 rows=40,001 width=153) (actual time=136,634.507..136,634.606 rows=193 loops=1)

  • Output: r_3.id12765, r_3.dim_total_metric12765, r_3.dimension12765[2], r_3.dimension12765[1], r_3.is_total, r_3.id, r_3.cnt_dim_values12765, r_3.dimension12765, r_3.metric12765
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
148. 0.230 136,634.554 ↑ 207.3 193 1

WindowAgg (cost=201,150.60..203,750.66 rows=40,001 width=121) (actual time=136,634.505..136,634.554 rows=193 loops=1)

  • Output: COALESCE((12765)), (COALESCE((12765)) IS NULL), (sum((count(r_4.through_first_good_comminication) FILTER (WHERE (((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)))))) OVER (?)), count((CASE WHEN (row_number() OVER (?) = 1) THEN ((SubPlan 3)) ELSE NULL::text[] END)) FILTER (WHERE (((SubPlan 3))[1] IS NOT NULL)) OVER (?), (12765), ((SubPlan 3)), NULL::text[], (count(r_4.through_first_good_comminication) FILTER (WHERE (((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)))))
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
149. 0.333 136,634.324 ↑ 207.3 193 1

WindowAgg (cost=201,150.60..203,150.65 rows=40,001 width=108) (actual time=136,633.806..136,634.324 rows=193 loops=1)

  • Output: (12765), ((SubPlan 3)), (count(r_4.through_first_good_comminication) FILTER (WHERE (((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL))))), (CASE WHEN (row_number() OVER (?) = 1) THEN ((SubPlan 3)) ELSE NULL::text[] END), sum((count(r_4.through_first_good_comminication) FILTER (WHERE (((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)))))) OVER (?)
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
150. 0.193 136,633.991 ↑ 207.3 193 1

WindowAgg (cost=201,150.60..202,050.62 rows=40,001 width=76) (actual time=136,633.776..136,633.991 rows=193 loops=1)

  • Output: (12765), ((SubPlan 3)), CASE WHEN (row_number() OVER (?) = 1) THEN ((SubPlan 3)) ELSE NULL::text[] END, (count(r_4.through_first_good_comminication) FILTER (WHERE (((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)))))
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
151. 0.731 136,633.798 ↑ 207.3 193 1

Sort (cost=201,150.60..201,250.60 rows=40,001 width=44) (actual time=136,633.769..136,633.798 rows=193 loops=1)

  • Output: (12765), ((SubPlan 3)), (count(r_4.through_first_good_comminication) FILTER (WHERE (((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)))))
  • Sort Key: (12765), ((SubPlan 3))
  • Sort Method: quicksort Memory: 55kB
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
152. 14,575.716 136,633.067 ↑ 207.3 193 1

MixedAggregate (cost=0.00..198,092.97 rows=40,001 width=44) (actual time=136,632.695..136,633.067 rows=193 loops=1)

  • Output: (12765), ((SubPlan 3)), count(r_4.through_first_good_comminication) FILTER (WHERE (((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL))))
  • Hash Key: 12,765, (SubPlan 3)
  • Group Key: ()
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
153. 109,129.219 122,057.351 ↓ 2.6 6,464,066 1

CTE Scan on w_source_query r_4 (cost=0.00..49,423.24 rows=2,471,162 width=72) (actual time=65,059.455..122,057.351 rows=6,464,066 loops=1)

  • Output: 12,765, (SubPlan 3), r_4.through_first_good_comminication, r_4.ac_name
  • Buffers: shared hit=4,412,247 read=13,220 dirtied=6, temp read=274,182 written=328,813
154.          

SubPlan (for CTE Scan)

155. 12,928.132 12,928.132 ↑ 1.0 1 6,464,066

Result (cost=0.02..0.03 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=6,464,066)

  • Output: ARRAY[COALESCE(r_4.ac_name, 'Неизвестная кампания'::text), NULL::text]
  • One-Time Filter: ((NOT (r_4.* IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'посетители без рекламной кампании'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)) AND ((NULLIF(lower(r_4.ac_name), ''::text) <> 'widget comagic'::text) OR (NULLIF(lower(r_4.ac_name), ''::text) IS NULL)))
156. 0.035 0.055 ↑ 1.0 1 1

GroupAggregate (cost=0.03..0.06 rows=1 width=36) (actual time=0.055..0.055 rows=1 loops=1)

  • Output: n.id, json_agg(json_build_object('dimension', n.dimension, 'on_date', n.on_date, 'metric', n.metric) ORDER BY n.priority) FILTER (WHERE ((n.dimension IS NOT NULL) OR (n.on_date IS NOT NULL) OR (n.metric <> '0'::numeric)))
  • Group Key: n.id
157. 0.005 0.020 ↑ 1.0 1 1

Sort (cost=0.03..0.04 rows=1 width=84) (actual time=0.020..0.020 rows=1 loops=1)

  • Output: n.id, n.dimension, n.on_date, n.metric, n.priority
  • Sort Key: n.id
  • Sort Method: quicksort Memory: 25kB
158. 0.015 0.015 ↑ 1.0 1 1

CTE Scan on w_no_grouping_sets n (cost=0.00..0.02 rows=1 width=84) (actual time=0.014..0.015 rows=1 loops=1)

  • Output: n.id, n.dimension, n.on_date, n.metric, n.priority
Planning time : 43.684 ms
Execution time : 136,911.646 ms