From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Spiros Ioannou <sivann(at)inaccess(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Largely inconsistent query execution speed, involving psql_tmp |
Date: | 2014-07-08 16:21:27 |
Message-ID: | CAMkU=1xNuYs-2bu4_jX3ect9iT6soqPwOAjtVz0g3UBLJALzTw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jul 8, 2014 at 2:47 AM, Spiros Ioannou <sivann(at)inaccess(dot)com> wrote:
> While executing the following query through psql :
>
> SELECT me.* FROM measurement_events me JOIN msrcs_timestamps mt ON
> me.measurement_source_id=mt.measurement_source_id WHERE measurement_time >
> last_update_time
>
> there are two behaviors observed by postgresql (8.4):
> 1) Either the query performs lots of reads on the database and completes
> in about 4 hours (that is the normal-expected behavior)
> 2) Either the query starts filling-up pgsql_tmp and this causes large
> write I/O on the server, and the query never actually completes on a
> reasonable time (we stop it after 10h).
>
> For some strange reason, behaviour 2 is always observed when running psql
> through a bash script, while behavior 1 is only observed while running psql
> interactively from command line (but not always).
>
> explain:
> # explain SELECT me.* FROM measurement_events me JOIN msrcs_timestamps mt
> ON me.measurement_source_id=mt.measurement_source_id WHERE
> measurement_time > last_update_time;
> QUERY PLAN
>
>
> ----------------------------------------------------------------------------------------------
> Hash Join (cost=10111.78..422893652.69 rows=2958929695 width=103)
> Hash Cond: (me.measurement_source_id = mt.measurement_source_id)
> Join Filter: (me.measurement_time > mt.last_update_time)
> -> Seq Scan on measurement_events me (cost=0.00..234251772.85
> rows=8876789085 width=103)
> -> Hash (cost=5733.57..5733.57 rows=350257 width=24)
> -> Seq Scan on msrcs_timestamps mt (cost=0.00..5733.57
> rows=350257 width=24)
> (6 rows)
>
Is this plan from a situation where it would probably take 4 hours, or from
the situation where it would probably fail to complete in 10 hours?
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Ravi Kiran | 2014-07-08 16:40:57 | debugging with gdb in postgres |
Previous Message | Andy Colson | 2014-07-08 15:10:49 | Re: Largely inconsistent query execution speed, involving psql_tmp |