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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: BUG #16148: Query on Large table hangs in ETL flows and gives outof memory when run in pgAdmin4
Дата
Msg-id CAMkU=1z29M7EC+BYKp06aCm69zw_rsz3u5zFtFRmuNgq8+fAHg@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #16148: Query on Large table hangs in ETL flows and gives out of memory when run in pgAdmin4  (PG Bug reporting form <noreply@postgresql.org>)
Ответы Re: BUG #16148: Query on Large table hangs in ETL flows and gives outof memory when run in pgAdmin4  (Scott Volkers <scottvolkers@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
On Wed, Dec 4, 2019 at 9:20 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      16148
Logged by:          Scott Volkers
Email address:      scottvolkers@gmail.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.

Cheers,

Jeff

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Stephen Frost
Дата:
Сообщение: Re: BUG #16041: Error shows up both in pgAdmin and in Ruby (pg gem)- Segmentation fault
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #16149: Prepared COPY queries always report 0 parameters when described