agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedMemory exhaustion on large query
4+ messages / 3 participants
[nested] [flat]
* Memory exhaustion on large query
@ 2021-12-12 18:25 Brice André <brice@famille-andre.be>
0 siblings, 2 replies; 4+ messages in thread
From: Brice André @ 2021-12-12 18:25 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am using postgresql 13, on debian server. I use libpq to interface my DB.
On some queries, my program is using so much memory that the application
crashes.
I use libpq in asynchronous mode to avoid this problem, but the memory is
consumed in the PQsendQueryPrepared call, before I invoke the first time
PQgetResult
I copy-pasted the query that causes the trouble here under. My table has an
index that should be usable for the query 'including the "ORDER BY" clause:
CREATE INDEX "ConfigurableWindowTableDataBuffer_last_modified_index" ON
"ConfigurableWindowTableDataBufferInternal" USING btree
("DbSyncLastModifiedBackupVersion", "DbSyncLastModifiedTimeStamp")
And an EXPLAIN on the query shows that the planner uses this index:
Index Scan using
"ConfigurableWindowTableDataBuffer_last_modified_index" on
"ConfigurableWindowTableDataBufferInternal" (cost=0.54..2870.78
rows=14059 width=510)
Index Cond: (("DbSyncLastModifiedBackupVersion" =
"DbSyncGetBackupVersion"()) AND ("DbSyncLastModifiedTimeStamp" > 0))
The content of the table is so huge that, if PQsendQueryPrepared retrieves
all data, or if postgresql engine is creating temp file with all data, the
query cannot succeed (I have no enough RAM or disk space to copy the whole
data). But I was expecting that using PQsendQueryPrepared and PQgetResult
would avoid this by returning one result at a time.
Any idea of what is going wrong ? Or on how I could correct my query to
avoid this ?
Note that basically, what I want to do is retrieve each record at a time,
and send its content on an open TCP connection. I am hoping to be allowed
to do that without requiring to have RAM or disk space to temporarily store
all data that needs to be sent.
Many thanks,
Brice
SELECT
"DbSyncID","DbSyncInsertedBackupVersion","DbSyncInsertedClientUniqueId","DbSyncInsertedClientEntryId","DbSyncDeleted","DbSyncRemovedFromServer","DbSyncLastModifiedBackupVersion","DbSyncLastModifiedTimeStamp","DbSyncFKBackupVersion","DbSyncFKClientUniqueId","DbSyncFKClientEntryId","TableDataID_BV","TableDataID_CID","TableDataID_CEID","TableId","SubTableId","BufferEntries_0"
FROM "ConfigurableWindowTableDataBufferSync" WHERE
"DbSyncLastModifiedBackupVersion"="DbSyncGetBackupVersion"() AND
"DbSyncLastModifiedTimeStamp" > 0 ORDER BY "DbSyncLastModifiedTimeStamp" ASC
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Memory exhaustion on large query
@ 2021-12-12 18:33 Erik Brandsberg <erik@heimdalldata.com>
parent: Brice André <brice@famille-andre.be>
1 sibling, 0 replies; 4+ messages in thread
From: Erik Brandsberg @ 2021-12-12 18:33 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: pgsql-sql
At first glance, the issue is the order by, which will use a temporary
table to sort the result. If you remove this, does the memory issue go
away?
On Sun, Dec 12, 2021 at 1:26 PM Brice André <brice@famille-andre.be> wrote:
> Hello,
>
> I am using postgresql 13, on debian server. I use libpq to interface my
> DB. On some queries, my program is using so much memory that the
> application crashes.
>
> I use libpq in asynchronous mode to avoid this problem, but the memory is
> consumed in the PQsendQueryPrepared call, before I invoke the first time
> PQgetResult
>
> I copy-pasted the query that causes the trouble here under. My table has
> an index that should be usable for the query 'including the "ORDER BY"
> clause:
> CREATE INDEX "ConfigurableWindowTableDataBuffer_last_modified_index" ON
> "ConfigurableWindowTableDataBufferInternal" USING btree
> ("DbSyncLastModifiedBackupVersion", "DbSyncLastModifiedTimeStamp")
>
> And an EXPLAIN on the query shows that the planner uses this index:
>
> Index Scan using "ConfigurableWindowTableDataBuffer_last_modified_index" on "ConfigurableWindowTableDataBufferInternal" (cost=0.54..2870.78 rows=14059 width=510)
>
> Index Cond: (("DbSyncLastModifiedBackupVersion" = "DbSyncGetBackupVersion"()) AND ("DbSyncLastModifiedTimeStamp" > 0))
>
>
> The content of the table is so huge that, if PQsendQueryPrepared retrieves
> all data, or if postgresql engine is creating temp file with all data, the
> query cannot succeed (I have no enough RAM or disk space to copy the whole
> data). But I was expecting that using PQsendQueryPrepared and PQgetResult
> would avoid this by returning one result at a time.
>
> Any idea of what is going wrong ? Or on how I could correct my query to
> avoid this ?
>
> Note that basically, what I want to do is retrieve each record at a time,
> and send its content on an open TCP connection. I am hoping to be allowed
> to do that without requiring to have RAM or disk space to temporarily store
> all data that needs to be sent.
>
> Many thanks,
> Brice
>
> SELECT
> "DbSyncID","DbSyncInsertedBackupVersion","DbSyncInsertedClientUniqueId","DbSyncInsertedClientEntryId","DbSyncDeleted","DbSyncRemovedFromServer","DbSyncLastModifiedBackupVersion","DbSyncLastModifiedTimeStamp","DbSyncFKBackupVersion","DbSyncFKClientUniqueId","DbSyncFKClientEntryId","TableDataID_BV","TableDataID_CID","TableDataID_CEID","TableId","SubTableId","BufferEntries_0"
> FROM "ConfigurableWindowTableDataBufferSync" WHERE
> "DbSyncLastModifiedBackupVersion"="DbSyncGetBackupVersion"() AND
> "DbSyncLastModifiedTimeStamp" > 0 ORDER BY "DbSyncLastModifiedTimeStamp" ASC
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Memory exhaustion on large query
@ 2021-12-12 18:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brice André <brice@famille-andre.be>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-12-12 18:43 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: pgsql-sql
=?UTF-8?B?QnJpY2UgQW5kcsOp?= <brice@famille-andre.be> writes:
> The content of the table is so huge that, if PQsendQueryPrepared retrieves
> all data, or if postgresql engine is creating temp file with all data, the
> query cannot succeed (I have no enough RAM or disk space to copy the whole
> data). But I was expecting that using PQsendQueryPrepared and PQgetResult
> would avoid this by returning one result at a time.
You're confusing asynchronous mode with single-row mode. Async mode,
per se, doesn't change memory consumption; it just lets you do something
else while waiting for the query result. You are (I suppose) missing
a call to PQsetSingleRowMode --- see
https://www.postgresql.org/docs/current/libpq-single-row-mode.html
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Memory exhaustion on large query
@ 2021-12-14 03:31 Brice André <brice@famille-andre.be>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Brice André @ 2021-12-14 03:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Many thanks for your help.
A call to PQsetSingleRowMode just after PQsendQueryPrepared solved my issue.
Regards,
Brice
Le dim. 12 déc. 2021 à 19:43, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> =?UTF-8?B?QnJpY2UgQW5kcsOp?= <brice@famille-andre.be> writes:
> > The content of the table is so huge that, if PQsendQueryPrepared
> retrieves
> > all data, or if postgresql engine is creating temp file with all data,
> the
> > query cannot succeed (I have no enough RAM or disk space to copy the
> whole
> > data). But I was expecting that using PQsendQueryPrepared and PQgetResult
> > would avoid this by returning one result at a time.
>
> You're confusing asynchronous mode with single-row mode. Async mode,
> per se, doesn't change memory consumption; it just lets you do something
> else while waiting for the query result. You are (I suppose) missing
> a call to PQsetSingleRowMode --- see
>
> https://www.postgresql.org/docs/current/libpq-single-row-mode.html
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-12-14 03:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-12 18:25 Memory exhaustion on large query Brice André <brice@famille-andre.be>
2021-12-12 18:33 ` Erik Brandsberg <erik@heimdalldata.com>
2021-12-12 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-14 03:31 ` Brice André <brice@famille-andre.be>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox