2014-10-06 17:46:17 YEKT:172.18.36.116(43827):aluuu@gits:[28846]: LOG: duration: 42.478 ms plan:
Query Text: SELECT
s.id,
COUNT(DISTINCT CASE WHEN '2014-10-06T17:46:17.795714+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-06T17:46:17.795714+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 time=42.377..42.442 rows=2 loops=1)
Buffers: shared hit=376 read=255
-> Sort (cost=866.95..866.97 rows=7 width=104) (actual time=42.148..42.153 rows=8 loops=1)
Sort Key: s.id
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=369 read=250
-> Nested Loop Left Join (cost=776.57..866.85 rows=7 width=104) (actual time=39.011..42.045 rows=8 loops=1)
Join Filter: (s.car_id = ccar.car_id)
Rows Removed by Join Filter: 480
Buffers: shared hit=366 read=250
-> Hash Right Join (cost=776.15..849.40 rows=7 width=104) (actual time=38.943..41.338 rows=8 loops=1)
Hash Cond: (contract_route.contract_id = winner_contract.id)
Buffers: shared hit=45 read=235
-> HashAggregate (cost=611.31..646.16 rows=2788 width=16) (actual time=35.900..38.627 rows=2967 loops=1)
Buffers: shared read=183
-> Seq Scan on contract_route (cost=0.00..468.54 rows=28554 width=16) (actual time=0.027..7.550 rows=28568 loops=1)
Buffers: shared read=183
-> Hash (cost=164.76..164.76 rows=7 width=72) (actual time=2.092..2.092 rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
Buffers: shared hit=45 read=52
-> Nested Loop Left Join (cost=1.41..164.76 rows=7 width=72) (actual time=0.887..2.082 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=45 read=52
-> Nested Loop Left Join (cost=1.12..162.96 rows=3 width=72) (actual time=0.592..0.982 rows=2 loops=1)
Buffers: shared hit=15 read=34
-> Nested Loop Left Join (cost=0.70..146.06 rows=2 width=56) (actual time=0.532..0.915 rows=2 loops=1)
Buffers: shared hit=10 read=32
-> Nested Loop (cost=0.42..129.44 rows=2 width=56) (actual time=0.519..0.893 rows=2 loops=1)
Buffers: shared hit=4 read=32
-> Append (cost=0.00..107.17 rows=5 width=64) (actual time=0.380..0.739 rows=2 loops=1)
Buffers: shared hit=1 read=28
-> Seq Scan on schedule s (cost=0.00..1.00 rows=1 width=64) (actual time=0.014..0.014 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 time=0.365..
0.723 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 read=28
-> 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 time=0.070..0.071 rows=1 loops=2)
Index Cond: ((auth_user_id = 271) AND (organization_id = s.executor_id))
Heap Fetches: 0
Buffers: shared hit=3 read=4
-> Index Scan using contract_pkey on contract ctr (cost=0.28..8.30 rows=1 width=16) (actual time=0.007..0.008 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 time=0.031..0.031 rows=0 loops=2)
Index Cond: (schedule_id = s.id)
Buffers: shared hit=5 read=2
-> Index Scan using contract_contractor_id_idx on contract winner_contract (cost=0.28..0.45 rows=6 width=24) (actual time=0.009..0.214 rows=69 loops=2)
Index Cond: (contractor_id = ctr.contractor_id)
Buffers: shared hit=30 read=18
-> 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 time=0.023..0.070 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 time=0.061..0.062 rows=1 loops=2)
Buffers: shared hit=7 read=5
-> Nested Loop (cost=0.58..16.63 rows=1 width=0) (actual time=0.058..0.058 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 time=0.033..0.033 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 time=0.021..0.021 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