On 11/21/2014 10:10 AM, Bill Moran wrote: > Are you saying that the _exact_ same query is executed about one > hundred times at approximately the same time? Hi, I am telling that the query got logged one hundred times in the csv log and also at the postgres log, so I am not sure if the query is executed multiple times or if this is just the same query that logs the query every time it create a temp file. I found someone on this list also reported a similar behaviour when doing joins: http://www.postgresql.org/message-id/52DFF90E.6060304@xxxxxxxxxx The machine is a supermicro X10SLM+-LN4F with an adaptec hardware raid 10 composed of 4 1tb sata drives and 16 GB ram. with the following tuning from the standard configuration: # /usr/bin/pgtune -i postgresql.conf.orig -D -c 400 -M 14935949312 -T Web maintenance_work_mem = 832MB # pgtune wizard 2014-11-18 checkpoint_completion_target = 0.7 # pgtune wizard 2014-11-18 effective_cache_size = 10GB # pgtune wizard 2014-11-18 work_mem = 32MB # pgtune wizard 2014-11-18 wal_buffers = 4MB # pgtune wizard 2014-11-18 checkpoint_segments = 8 # pgtune wizard 2014-11-18 shared_buffers = 3328MB # pgtune wizard 2014-11-18 max_connections = 400 # pgtune wizard 2014-11-18 On 11/21/2014 10:15 AM, Tom Lane wrote: > Could we see what EXPLAIN says about that? > > You might try EXPLAIN ANALYZE too, but we're expecting that to run out > of disk space :-(. > > I'm speculating wildly here, but I wonder whether it still runs > out of disk space if you remove the COALESCE((SELECT...)) output > expression. This is the explain, even before we do the coalesce we count the records first and in this query the system also experience that behaviour, but after a while if I execute the same query via pgsql console then it works nice, I was wrong when I tough I have found a common pattern but this is unpredictable, sometimes it creates temp files, sometimes not. look at this query (this use partitioning with table inheritance): explain WITH x AS ( SELECT COUNT(*) FROM public.device_basicgpsevent AS bev JOIN public.device_event AS ev ON ev.device_id = 356610000000438 AND ev.event_time BETWEEN '2014-11-13 23:00:00-05:00' AND '2014-11-15 00:59:59.999999-05:00' AND bev.event_ptr_id = ev.id AND bev.age IN (1, 2) AND ev.system_time BETWEEN '2014-11-11 23:00:00-05:00' AND '2014-11-17 00:59:59.999999-05:00' LEFT JOIN public.device_drivingmets AS dmev ON dmev.basicgpsevent_ptr_id = ev.id AND dmev.event_system_time BETWEEN '2014-11-11 23:00:00-05:00' AND '2014-11-17 00:59:59.999999-05:00' LEFT JOIN public.device_georefevent AS grev ON grev.basicgpsevent_ptr_id = ev.id AND grev.event_system_time BETWEEN '2014-11-11 23:00:00-05:00' AND '2014-11-17 00:59:59.999999-05:00' LEFT JOIN public.device_extendedgpsevent AS eev ON eev.basicgpsevent_ptr_id = ev.id AND eev.event_system_time BETWEEN '2014-11-11 23:00:00-05:00' AND '2014-11-17 00:59:59.999999-05:00' LEFT JOIN public.device_distance AS dev ON dev.extendedgpsevent_ptr_id = ev.id AND dev.event_system_time BETWEEN '2014-11-11 23:00:00-05:00' AND '2014-11-17 00:59:59.999999-05:00' LEFT JOIN public.device_photoevent AS pev ON pev.basicgpsevent_ptr_id = ev.id AND pev.event_system_time BETWEEN '2014-11-11 23:00:00-05:00' AND '2014-11-17 00:59:59.999999-05:00' WHERE TRUE AND bev.event_system_time BETWEEN '2014-11-11 23:00:00-05:00' AND '2014-11-17 00:59:59.999999-05:00' ) SELECT * FROM x ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CTE Scan on x (cost=6572056348.94..6572056348.96 rows=1 width=8) CTE x -> Aggregate (cost=6572056348.93..6572056348.94 rows=1 width=0) -> Hash Join (cost=34247633.75..5386910348.86 rows=474058400025 width=0) Hash Cond: (bev.event_ptr_id = ev.id) -> Append (cost=0.00..5737.68 rows=87152 width=4) -> Seq Scan on device_basicgpsevent bev (cost=0.00..33.80 rows=1 width=4) Filter: ((age = ANY ('{1,2}'::integer[])) AND (event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_basicgpsevent_2014w46 bev (cost=0.00..2837.03 rows=86972 width=4) Filter: ((age = ANY ('{1,2}'::integer[])) AND (event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_basicgpsevent_2014w47 bev (cost=0.00..2866.85 rows=179 width=4) Filter: ((age = ANY ('{1,2}'::integer[])) AND (event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Hash (cost=16399273.75..16399273.75 rows=1087900000 width=4) -> Merge Left Join (cost=9998.45..16399273.75 rows=1087900000 width=4) Merge Cond: (ev.id = dev.extendedgpsevent_ptr_id) -> Merge Left Join (cost=2057.27..63624.27 rows=3768353 width=4) Merge Cond: (ev.id = dmev.basicgpsevent_ptr_id) -> Nested Loop Left Join (cost=830.02..5677.74 rows=77594 width=4) Join Filter: (eev.basicgpsevent_ptr_id = ev.id) -> Merge Left Join (cost=830.02..831.70 rows=195 width=4) Merge Cond: (ev.id = grev.basicgpsevent_ptr_id) -> Merge Left Join (cost=796.35..797.42 rows=195 width=4) Merge Cond: (ev.id = pev.basicgpsevent_ptr_id) -> Sort (cost=770.49..770.98 rows=195 width=4) Sort Key: ev.id -> Append (cost=0.00..763.07 rows=195 width=4) -> Seq Scan on device_event ev (cost=0.00..27.32 rows=1 width=4) Filter: ((event_time >= '2014-11-13 23:00:00-05'::timestamp with time zone) AND (event_time <= '2014-11-15 00:59:59.999999-05'::timestamp with time zone) AND (system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone) AND (device_id = 356610000000438::bigint)) -> Bitmap Heap Scan on device_event_2014w46 ev (cost=7.32..727.43 rows=193 width=4) Recheck Cond: ((device_id = 356610000000438::bigint) AND (event_time >= '2014-11-13 23:00:00-05'::timestamp with time zone) AND (event_time <= '2014-11-15 00:59:59.999999-05'::timestamp with time zone)) Filter: ((system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Bitmap Index Scan on device_event_2014w46_device_id_event_time_idx (cost=0.00..7.28 rows=239 width=0) Index Cond: ((device_id = 356610000000438::bigint) AND (event_time >= '2014-11-13 23:00:00-05'::timestamp with time zone) AND (event_time <= '2014-11-15 00:59:59.999999-05'::timestamp with time zone)) -> Index Scan using device_event_2014w47_device_id_event_time_idx on device_event_2014w47 ev (cost=0.00..8.32 rows=1 width=4) Index Cond: ((device_id = 356610000000438::bigint) AND (event_time >= '2014-11-13 23:00:00-05'::timestamp with time zone) AND (event_time <= '2014-11-15 00:59:59.999999-05'::timestamp with time zone)) Filter: ((system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Sort (cost=25.86..25.88 rows=6 width=4) Sort Key: pev.basicgpsevent_ptr_id -> Append (cost=0.00..25.79 rows=6 width=4) -> Seq Scan on device_photoevent pev (cost=0.00..23.20 rows=4 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_photoevent_2014w46 pev (cost=0.00..1.48 rows=1 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_photoevent_2014w47 pev (cost=0.00..1.10 rows=1 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Sort (cost=33.67..33.69 rows=8 width=4) Sort Key: grev.basicgpsevent_ptr_id -> Append (cost=0.00..33.55 rows=8 width=4) -> Seq Scan on device_georefevent grev (cost=0.00..33.55 rows=8 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Append (cost=0.00..24.81 rows=3 width=4) -> Seq Scan on device_extendedgpsevent eev (cost=0.00..10.75 rows=1 width=4) Filter: ((eev.event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (eev.event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Index Scan using device_extendedgpsevent_2014w46_basicgpsevent_ptr_id_pkey on device_extendedgpsevent_2014w46 eev (cost=0.00..7.05 rows=1 width=4) Index Cond: (eev.basicgpsevent_ptr_id = ev.id) Filter: ((eev.event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (eev.event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Index Scan using device_extendedgpsevent_2014w47_basicgpsevent_ptr_id_pkey on device_extendedgpsevent_2014w47 eev (cost=0.00..7.01 rows=1 width=4) Index Cond: (eev.basicgpsevent_ptr_id = ev.id) Filter: ((eev.event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (eev.event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Sort (cost=1227.25..1251.53 rows=9713 width=4) Sort Key: dmev.basicgpsevent_ptr_id -> Append (cost=0.00..583.97 rows=9713 width=4) -> Seq Scan on device_drivingmets dmev (cost=0.00..35.50 rows=8 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_drivingmets_2014w46 dmev (cost=0.00..260.50 rows=9704 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_drivingmets_2014w47 dmev (cost=0.00..287.97 rows=1 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Sort (cost=7941.18..8085.52 rows=57738 width=4) Sort Key: dev.extendedgpsevent_ptr_id -> Append (cost=0.00..3374.90 rows=57738 width=4) -> Seq Scan on device_distance dev (cost=0.00..31.00 rows=7 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_distance_2014w46 dev (cost=0.00..1615.54 rows=57730 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) -> Seq Scan on device_distance_2014w47 dev (cost=0.00..1728.36 rows=1 width=4) Filter: ((event_system_time >= '2014-11-11 23:00:00-05'::timestamp with time zone) AND (event_system_time <= '2014-11-17 00:59:59.999999-05'::timestamp with time zone)) (77 rows) -- Typed on my key64.org keyboard Nestor A Diaz -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
