Re: Memory exhaustion on large query

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(at)famille-andre(dot)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
>
>