Re: pgsql_tmp consuming all inodes - Mailing list pgsql-general

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