2014-10-07 14:25:11 YEKT:172.18.36.116(35880):aluuu@gits:[9028]: LOG: duration: 250.731 ms plan:
Query Text: SELECT
s.id,
COUNT(DISTINCT CASE WHEN '2014-10-07T14:25:11.366715+06:00'::timestamptz BETWEEN sp.time_plan_start AND sp.time_plan_end THEN s.id END) as problem,
COUNT(DISTINCT CASE WHEN '2014-10-07T14:25:11.366715+06:00'::timestamptz < s.time_of_start THEN s.id END) as unstarted,
COUNT(DISTINCT CASE WHEN s.time_of_end = sp.time_plan_end THEN s.id END) as problem_at_end,
COUNT(DISTINCT CASE WHEN s.time_of_start = sp.time_plan_start AND s.time_of_end = sp.time_plan_end THEN s.id END) as problem_fullday,
COUNT(DISTINCT CASE WHEN (s.car_id IS NULL OR ccar.id IS NOT NULL) and s.route_id = any(winner_routes.r) THEN s.id END) as contract_not_violated,
---для отметки о том что низкопольный автобус
(SELECT COUNT(*)
FROM schedule_template_order sto JOIN schedule_template st ON sto.id = st.schedule_template_order_id
WHERE st.id = s.schedule_template_id AND sto.cripple = true
) cripple
FROM schedule s
LEFT JOIN schedule_problem sp ON sp.schedule_id = s.id
LEFT JOIN problem pr ON pr.id = sp.problem_id
-- определяем нарушение договора: использование машин не из договоров победителя лота
LEFT JOIN contract ctr ON ctr.id = s.contract_id
LEFT JOIN contract as winner_contract ON winner_contract.contractor_id = ctr.contractor_id
AND DATE(s.time_of_start at time zone 'Etc/GMT-6') BETWEEN winner_contract.date_begin AND winner_contract.date_end
LEFT JOIN contract_car ccar ON ccar.contract_id = winner_contract.id AND s.car_id = ccar.car_id
-- для проверки на то что winner_contract может планировать этот маршрут
LEFT JOIN (SELECT ARRAY_AGG(route_id) AS r, contract_id FROM contract_route GROUP BY contract_id ) winner_routes ON winner_routes.contract_id = winner_contract.id
JOIN user_organization uo ON uo.auth_user_id = 271
AND uo.organization_id = s.executor_id
WHERE s.time_of_start BETWEEN '2014-09-24T00:00:00+06:00'::timestamptz AND '2014-09-24T23:59:59+06:00'::timestamptz
AND s.route_id = 118
GROUP BY s.id
ORDER BY s.id
GroupAggregate (cost=866.95..950.53 rows=5 width=104) (actual rows=2 loops=1)
Buffers: shared hit=401 read=230 dirtied=3
-> Sort (cost=866.95..866.97 rows=7 width=104) (actual rows=8 loops=1)
Sort Key: s.id
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=394 read=225 dirtied=3
-> Nested Loop Left Join (cost=776.57..866.85 rows=7 width=104) (actual rows=8 loops=1)
Join Filter: (s.car_id = ccar.car_id)
Rows Removed by Join Filter: 480
Buffers: shared hit=391 read=225 dirtied=3
-> Hash Right Join (cost=776.15..849.40 rows=7 width=104) (actual rows=8 loops=1)
Hash Cond: (contract_route.contract_id = winner_contract.id)
Buffers: shared hit=70 read=210 dirtied=3
-> HashAggregate (cost=611.31..646.16 rows=2788 width=16) (actual rows=2967 loops=1)
Buffers: shared read=183
-> Seq Scan on contract_route (cost=0.00..468.54 rows=28554 width=16) (actual rows=28568 loops=1)
Buffers: shared read=183
-> Hash (cost=164.76..164.76 rows=7 width=72) (actual rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
Buffers: shared hit=70 read=27 dirtied=3
-> Nested Loop Left Join (cost=1.41..164.76 rows=7 width=72) (actual rows=8 loops=1)
Join Filter: ((date(timezone('Etc/GMT-6'::text, s.time_of_start)) >= winner_contract.date_begin) AND (date(timezone('Etc/GMT-6'::text, s.time_of_start)) <= winner_contract.date_end))
Rows Removed by Join Filter: 130
Buffers: shared hit=70 read=27 dirtied=3
-> Nested Loop Left Join (cost=1.12..162.96 rows=3 width=72) (actual rows=2 loops=1)
Buffers: shared hit=22 read=27 dirtied=3
-> Nested Loop Left Join (cost=0.70..146.06 rows=2 width=56) (actual rows=2 loops=1)
Buffers: shared hit=17 read=25 dirtied=1
-> Nested Loop (cost=0.42..129.44 rows=2 width=56) (actual rows=2 loops=1)
Buffers: shared hit=11 read=25 dirtied=1
-> Append (cost=0.00..107.17 rows=5 width=64) (actual rows=2 loops=1)
Buffers: shared hit=4 read=25 dirtied=1
-> Seq Scan on schedule s (cost=0.00..1.00 rows=1 width=64) (actual rows=0 loops=1)
Filter: ((time_of_start >= '2014-09-23 18:00:00+00'::timestamp with time zone) AND (time_of_start <= '2014-09-24 17:59:59+00'::timestamp with time zone) AND (route_id = 118))
Buffers: shared hit=1
-> Index Scan using schedule_2014_07_start_route_order_smena_idx on schedule_2014_07 s_1 (cost=0.42..106.17 rows=4 width=64) (actual rows=2 loops=1)
Index Cond: ((time_of_start >= '2014-09-23 18:00:00+00'::timestamp with time zone) AND (time_of_start <= '2014-09-24 17:59:59+00'::timestamp with time zone) AND (route_id = 118))
Buffers: shared hit=3 read=25 dirtied=1
-> Index Only Scan using user_organization_auth_user_id_organization_id_idx on user_organization uo (cost=0.42..4.45 rows=1 width=8) (actual rows=1 loops=2)
Index Cond: ((auth_user_id = 271) AND (organization_id = s.executor_id))
Heap Fetches: 0
Buffers: shared hit=7
-> Index Scan using contract_pkey on contract ctr (cost=0.28..8.30 rows=1 width=16) (actual rows=1 loops=2)
Index Cond: (id = s.contract_id)
Buffers: shared hit=6
-> Index Scan using schedule_problem_schedule_id_idx on schedule_problem sp (cost=0.42..8.44 rows=1 width=32) (actual rows=0 loops=2)
Index Cond: (schedule_id = s.id)
Buffers: shared hit=5 read=2 dirtied=2
-> Index Scan using contract_contractor_id_idx on contract winner_contract (cost=0.28..0.45 rows=6 width=24) (actual rows=69 loops=2)
Index Cond: (contractor_id = ctr.contractor_id)
Buffers: shared hit=48
-> Index Scan using contract_car_contract_id_car_id_route_id_order_idx on contract_car ccar (cost=0.41..2.12 rows=30 width=24) (actual rows=60 loops=8)
Index Cond: (contract_id = winner_contract.id)
Buffers: shared hit=321 read=15
SubPlan 1
-> Aggregate (cost=16.63..16.64 rows=1 width=0) (actual rows=1 loops=2)
Buffers: shared hit=7 read=5
-> Nested Loop (cost=0.58..16.63 rows=1 width=0) (actual rows=0 loops=2)
Buffers: shared hit=7 read=5
-> Index Scan using schedule_template_pkey on schedule_template st (cost=0.29..8.31 rows=1 width=8) (actual rows=1 loops=2)
Index Cond: (id = s.schedule_template_id)
Buffers: shared hit=3 read=3
-> Index Scan using schedule_template_order_pkey on schedule_template_order sto (cost=0.29..8.31 rows=1 width=8) (actual rows=0 loops=2)
Index Cond: (id = st.schedule_template_order_id)
Filter: cripple
Rows Removed by Filter: 1
Buffers: shared hit=4 read=2