Re: pgsql_tmp consuming all inodes

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@redhat.com

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