Re: BUG #16148: Query on Large table hangs in ETL flows and gives out of memory when run in pgAdmin4

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: scottvolkers(at)gmail(dot)com, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #16148: Query on Large table hangs in ETL flows and gives out of memory when run in pgAdmin4
Date: 2019-12-04 22:05:21
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

On Wed, Dec 4, 2019 at 9:20 AM PG Bug reporting form <noreply(at)postgresql(dot)org>

> The following bug has been logged on the website:
> Bug reference: 16148
> Logged by: Scott Volkers
> Email address: scottvolkers(at)gmail(dot)com
> PostgreSQL version: 9.5.15
> Operating system: PostgreSQL 9.5.15 on x86_64-pc-linux-gnu, compiled
> Description:
> We have a large table and the error occurs with this where clause:
> FROM "elliedb"."documentlog" WHERE dcmodifiedutc>(extract(epoch from
> TIMESTAMP '2019-11-15 11:30:51')*1000)
> When we reduce the scope to current time - 4 hours the query works within
> 44
> seconds.
> where dcmodifiedutc > '1575282651000'
> Is this expected? Is this a version issue being only 9.5?

From "Now minus 4" hours to now covers 100 fold less time than from
2019-11-15 11:30:51 until now does. Assuming your data is evenly
distributed over the past and doesn't have data from the future, then I
think that yes, selecting 100 time more data is expected to take more time
and more memory. pgAdmin4 is not well suited to loading giant data sets
into memory. You can extract large data sets directly into files. This
will not depend on the version.

> It seems the
> timestamp conversion would be done once and applied to the filter, but it
> seems to ballooning the query result being aggregated for the where
> clause?
Is aggregation being used? You haven't shown any aggregation.



In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-12-05 00:21:36 BUG #16149: Prepared COPY queries always report 0 parameters when described
Previous Message Stephen Frost 2019-12-04 19:34:40 Re: BUG #16041: Error shows up both in pgAdmin and in Ruby (pg gem) - Segmentation fault