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