Thread: pgsql_tmp consuming all inodes
Hello People. I have installed a postgres engine: PostgreSQL 8.4.22 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.7.2-5) 4.7.2, 64-bit from debian packages at postgresql official repository running on debian wheezy 7.7 64 bit. I have a couple of databases running on a cluster, and two of them have been experiencing the following behavior since installed yesterday: They create a lot of files under directory "pgsql_tmp" up to the point they consume all the inodes, when I do a 'df -i' I found the partition assigned to the tablespace where the database is located is out of inodes. I don't know what postgres internals trigger this behavior but at least we can replicate the behavior with a query. Python database clients got this error message: "DatabaseError: could not create temporary file "pg_tblspc/16575/pgsql_tmp/pgsql_tmp27225.105148": No space left on device" I work on postgresql 8.4 because of issues we found working with 9.1 when it comes to partitioning, however we can not switch to 9.3 because we use postgis 1.5 which is supported up to 9.1. On the other side our experience with 8.4 have been good, except that today the database goes in recovery mode due to an out of memory error: Nov 20 09:10:52 feline1 kernel: [125090.855205] Out of memory: Kill process 14314 (postgres) score 145 or sacrifice child Nov 20 09:10:52 feline1 kernel: [125090.855267] Killed process 14314 (postgres) total-vm:3579868kB, anon-rss:2708kB, file-rss:2668600kB Nov 20 09:10:52 feline1 kernel: [125091.592295] postgres invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0, oom_score_adj=0 Nov 20 09:10:52 feline1 kernel: [125091.592367] postgres cpuset=/ mems_allowed=0 Nov 20 09:10:52 feline1 kernel: [125091.592402] Pid: 21339, comm: postgres Tainted: G O 3.2.0-4-amd64 #1 Debian 3.2.63-2+deb7u1 Any ideas on how to fix the creation of too much files under 'pgsql_tmp' directory ? Any hint on the Out of memory error ? Slds. -- Nestor A Diaz
"Nestor A. Diaz" <nestor@tiendalinux.com> writes:
> I have a couple of databases running on a cluster, and two of them have
> been experiencing the following behavior since installed yesterday:
> They create a lot of files under directory "pgsql_tmp" up to the point
> they consume all the inodes, when I do a 'df -i' I found the partition
> assigned to the tablespace where the database is located is out of inodes.
I'd bet money that what you've got is a broken query, ie something that's
trying to compute a ridiculously large join. PG will faithfully try to
do it, until it runs out of disk space to hold the intermediate result.
It's not exactly hard to do that in SQL --- forget a join condition,
say, and suddenly you've got a Cartesian-product intermediate result.
> On the other side our experience with 8.4 have been good, except that
> today the database goes in recovery mode due to an out of memory error:
Sounds a bit like the same problem in a different guise.
regards, tom lane
On 11/20/2014 08:20 AM, Nestor A. Diaz wrote: > Hello People. > > I have installed a postgres engine: PostgreSQL 8.4.22 on > x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.7.2-5) 4.7.2, > 64-bit from debian packages at postgresql official repository running on > debian wheezy 7.7 64 bit. > > I have a couple of databases running on a cluster, and two of them have > been experiencing the following behavior since installed yesterday: > > They create a lot of files under directory "pgsql_tmp" up to the point > they consume all the inodes, when I do a 'df -i' I found the partition > assigned to the tablespace where the database is located is out of inodes. > > I don't know what postgres internals trigger this behavior but at least > we can replicate the behavior with a query. What query? How is it executed? > > Python database clients got this error message: > > "DatabaseError: could not create temporary file > "pg_tblspc/16575/pgsql_tmp/pgsql_tmp27225.105148": No space left on device" > > I work on postgresql 8.4 because of issues we found working with 9.1 > when it comes to partitioning, however we can not switch to 9.3 because > we use postgis 1.5 which is supported up to 9.1. > > On the other side our experience with 8.4 have been good, except that > today the database goes in recovery mode due to an out of memory error: > > Nov 20 09:10:52 feline1 kernel: [125090.855205] Out of memory: Kill > process 14314 (postgres) score 145 or sacrifice child > Nov 20 09:10:52 feline1 kernel: [125090.855267] Killed process 14314 > (postgres) total-vm:3579868kB, anon-rss:2708kB, file-rss:2668600kB > Nov 20 09:10:52 feline1 kernel: [125091.592295] postgres invoked > oom-killer: gfp_mask=0x280da, order=0, oom_adj=0, oom_score_adj=0 > Nov 20 09:10:52 feline1 kernel: [125091.592367] postgres cpuset=/ > mems_allowed=0 > Nov 20 09:10:52 feline1 kernel: [125091.592402] Pid: 21339, comm: > postgres Tainted: G O 3.2.0-4-amd64 #1 Debian 3.2.63-2+deb7u1 Well this is coming from the kernel OOM(Out Of Memory) manager. For how to deal with this see here: http://www.postgresql.org/docs/8.4/static/kernel-resources.html#AEN24213 > > Any ideas on how to fix the creation of too much files under 'pgsql_tmp' > directory ? Any hint on the Out of memory error ? > > Slds. > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/20/2014 12:18 PM, Adrian Klaver wrote:
> What query?
>
> How is it executed?
Hi Adrian, this is one of the queries that appear to consume all
resources, we use a CTE approach ("with") because in 9.1 _sometimes_ the
planner perform an order by before doing the joins something that was
killing the performance of the app.
I think it could be a problem of the web app, I turned on the 'csvlog'
to diagnose the problem and I found that at the same time the query get
executes one hundred times aprox.
This is what got logged at the csv (look at the time, different seconds):
2014-11-20 09:01:18.493
COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35849,"SELECT",2014-11-20
08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.103510"", size 484",,,,,,"
2014-11-20 09:01:18.496
COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35850,"SELECT",2014-11-20
08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.34356"", size 220",,,,,,"
[...]
As you can see from above it creates a lots of temp files for the same
query.
And finally the query is this:
WITH x AS
( SELECT event_time AS x_event_time,
system_time AS x_system_time,
id,
code,
metric,
event_time,
system_time,
bev.lat/1e5 AS f_lat,
bev.lon/1e5 AS f_lon,
ROUND(bev.mph*1 ,1) AS speed,
dev.increment AS distance_increment,
head,
age,
device_id,
ecu_total_fuel,
ecu_total_fuel_flag,
ecu_idle_fuel,
ecu_idle_fuel_flag,
(NOT (bev.lat = 0
AND bev.lon = 0)
AND bev.age = 2
AND bev.source <= 8) AS valid_position,
eev.hdop/100.0 AS f_hdop,
georef_id,
photo_id,
pev.status AS photo_status,
grev.type_id AS georef_evtype,
COALESCE (
(SELECT dc.ky_id
FROM public.device_deviceconfiguration AS dc
WHERE dc.device_id = ev.device_id
AND dc.time <= ev.system_time
AND dc.state = 3
ORDER BY dc.time DESC LIMIT 1), '0000' ) AS ky,
event_type
FROM public.device_basicgpsevent AS bev
JOIN public.device_event AS ev ON ev.device_id = 400612026000000
AND ev.event_time BETWEEN '2014-11-19 23:00:00-05:00' AND '2014-11-21 00:59:59.999999-05:00'
AND bev.event_ptr_id = ev.id
AND bev.age IN (1,
2)
LEFT JOIN public.device_drivingmets AS dmev ON dmev.basicgpsevent_ptr_id = ev.id
LEFT JOIN public.device_georefevent AS grev ON grev.basicgpsevent_ptr_id = ev.id
LEFT JOIN public.device_extendedgpsevent AS eev ON eev.basicgpsevent_ptr_id = ev.id
LEFT JOIN public.device_distance AS dev ON dev.extendedgpsevent_ptr_id = ev.id
LEFT JOIN public.device_photoevent AS pev ON pev.basicgpsevent_ptr_id = ev.id
WHERE TRUE )
SELECT *
FROM x
ORDER BY x.x_event_time ,
x.x_system_time ;
>
> Well this is coming from the kernel OOM(Out Of Memory) manager.
>
> For how to deal with this see here:
>
> http://www.postgresql.org/docs/8.4/static/kernel-resources.html#AEN24213
Thanks for the suggestion.
Slds.
--
Typed on my key64.org keyboard
Nestor A Diaz
On Fri, 21 Nov 2014 09:54:43 -0500
"Nestor A. Diaz" <nestor@tiendalinux.com> wrote:
> On 11/20/2014 12:18 PM, Adrian Klaver wrote:
> > What query?
> >
> > How is it executed?
>
> Hi Adrian, this is one of the queries that appear to consume all
> resources, we use a CTE approach ("with") because in 9.1 _sometimes_ the
> planner perform an order by before doing the joins something that was
> killing the performance of the app.
>
> I think it could be a problem of the web app, I turned on the 'csvlog'
> to diagnose the problem and I found that at the same time the query get
> executes one hundred times aprox.
Are you saying that the _exact_ same query is executed about one hundred
times at approximately the same time?
If this is the case, you'll experience great improvements all around if you
advise your developers that the RDBMS is not RAM, and encourage them to
use a proper MVC model where the model != the RDBMS.
I've worked with this kind of problem repeatedly, and fixing it not only
avoids resource exhaustion problems like you're seeing, but it improves the
performance of the system significantly.
> This is what got logged at the csv (look at the time, different seconds):
>
> 2014-11-20 09:01:18.493
> COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35849,"SELECT",2014-11-20
> 08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
> ""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.103510"", size 484",,,,,,"
> 2014-11-20 09:01:18.496
> COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35850,"SELECT",2014-11-20
> 08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
> ""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.34356"", size 220",,,,,,"
>
> [...]
>
> As you can see from above it creates a lots of temp files for the same
> query.
>
> And finally the query is this:
>
> WITH x AS
> ( SELECT event_time AS x_event_time,
> system_time AS x_system_time,
> id,
> code,
> metric,
> event_time,
> system_time,
> bev.lat/1e5 AS f_lat,
> bev.lon/1e5 AS f_lon,
> ROUND(bev.mph*1 ,1) AS speed,
> dev.increment AS distance_increment,
> head,
> age,
> device_id,
> ecu_total_fuel,
> ecu_total_fuel_flag,
> ecu_idle_fuel,
> ecu_idle_fuel_flag,
> (NOT (bev.lat = 0
> AND bev.lon = 0)
> AND bev.age = 2
> AND bev.source <= 8) AS valid_position,
> eev.hdop/100.0 AS f_hdop,
> georef_id,
> photo_id,
> pev.status AS photo_status,
> grev.type_id AS georef_evtype,
> COALESCE (
> (SELECT dc.ky_id
> FROM public.device_deviceconfiguration AS dc
> WHERE dc.device_id = ev.device_id
> AND dc.time <= ev.system_time
> AND dc.state = 3
> ORDER BY dc.time DESC LIMIT 1), '0000' ) AS ky,
> event_type
> FROM public.device_basicgpsevent AS bev
> JOIN public.device_event AS ev ON ev.device_id = 400612026000000
> AND ev.event_time BETWEEN '2014-11-19 23:00:00-05:00' AND '2014-11-21 00:59:59.999999-05:00'
> AND bev.event_ptr_id = ev.id
> AND bev.age IN (1,
> 2)
> LEFT JOIN public.device_drivingmets AS dmev ON dmev.basicgpsevent_ptr_id = ev.id
> LEFT JOIN public.device_georefevent AS grev ON grev.basicgpsevent_ptr_id = ev.id
> LEFT JOIN public.device_extendedgpsevent AS eev ON eev.basicgpsevent_ptr_id = ev.id
> LEFT JOIN public.device_distance AS dev ON dev.extendedgpsevent_ptr_id = ev.id
> LEFT JOIN public.device_photoevent AS pev ON pev.basicgpsevent_ptr_id = ev.id
> WHERE TRUE )
> SELECT *
> FROM x
> ORDER BY x.x_event_time ,
> x.x_system_time ;
>
>
> >
> > Well this is coming from the kernel OOM(Out Of Memory) manager.
> >
> > For how to deal with this see here:
> >
> > http://www.postgresql.org/docs/8.4/static/kernel-resources.html#AEN24213
>
> Thanks for the suggestion.
>
> Slds.
>
> --
> Typed on my key64.org keyboard
>
> Nestor A Diaz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Bill Moran
I need your help to succeed:
http://gamesbybill.com
"Nestor A. Diaz" <nestor@tiendalinux.com> writes:
> As you can see from above it creates a lots of temp files for the same
> query.
> And finally the query is this:
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.
regards, tom lane
On 11/21/2014 06:54 AM, Nestor A. Diaz wrote:
> On 11/20/2014 12:18 PM, Adrian Klaver wrote:
>> What query?
>>
>> How is it executed?
>
> Hi Adrian, this is one of the queries that appear to consume all
> resources, we use a CTE approach ("with") because in 9.1 _sometimes_ the
> planner perform an order by before doing the joins something that was
> killing the performance of the app.
>
> I think it could be a problem of the web app, I turned on the 'csvlog'
> to diagnose the problem and I found that at the same time the query get
> executes one hundred times aprox.
To me it does not look the query is executed one hundred times, it looks
one hundred temp files are created for the query.
>
> This is what got logged at the csv (look at the time, different seconds):
>
> 2014-11-20 09:01:18.493
> COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35849,"SELECT",2014-11-20
> 08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
> ""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.103510"", size 484",,,,,,"
> 2014-11-20 09:01:18.496
> COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35850,"SELECT",2014-11-20
> 08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
> ""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.34356"", size 220",,,,,,"
>
> [...]
>
> As you can see from above it creates a lots of temp files for the same
> query.
So what is the size of the dataset you are working with and what is the
size of the dataset you expect to return?
What are the hardware specs for the machine you are using, in particular
the amount of memory?
Are there other resource intensive programs running on this machine?
>
> Slds.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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
"Nestor A. Diaz" <nestor@tiendalinux.com> writes:
> On 11/21/2014 10:15 AM, Tom Lane wrote:
>> Could we see what EXPLAIN says about that?
> look at this query (this use partitioning with table inheritance):
I asked for an EXPLAIN of the problematic query, not something weakly
related to it :-(. However, if these rowcount estimates are anywhere close
to reality, it's not exactly surprising that you're seeing huge amounts
of temporary storage:
> -> Hash Join (cost=34247633.75..5386910348.86 rows=474058400025 width=0)
> ...
> -> Hash (cost=16399273.75..16399273.75 rows=1087900000 width=4)
The hash join is estimated to need to put a billion rows into its hash
table and then to return nearly 500 billion rows --- which, in your
original query, would need to be sorted. Even if the hash table didn't
spill to disk, the sort certainly would, unless these estimates are off
by a lot of orders of magnitude.
So as was asked upthread, just how much data are you expecting this
query to return? Are you sure you've got the join conditions right?
It's evidently the joins to "dev" and "bev" that the planner thinks
are going to cause huge multiplications of the number of rows, so
perhaps those are the ones to look at closely.
regards, tom lane