Re: [PERFORM] Correct use of cursors for very large result sets in Postgres

[Mike Beaton]

Dear Tom,

This is very helpful, thank you.

You make a very useful point that the limitation is basically on PL/pgSQL
and other PL languages. And someone on SO already pointed out that an
inline SQL function with a enormous sized TABLE return value also doesn't
have any buffering problems. So that's a very convenient option, whenever
SQL alone is powerful enough.

You make the further very helpful point that any library which is written
using `libpq` won't work as desired on `FETCH ALL FROM HugeCursor`. But I
don't know whether that's 'most' libraries. I think that depends on your
programming milieu! I'm working in the world of ADO.NET (but the same seems
to apply to JDBC) where most low level drivers are not written using
`libpq` but rather directly with sockets against the database - which makes
sense because a streaming data reader is part of the contract which those
drivers have to implement.

It's definitely worth noting that the `FETCH 10 FROM cursor` until
exhausted pattern will *always* be safe. But most fundamentally I did, very
specifically, want to know if the `FETCH ALL FROM
CursorToAstronomicallyLargeData` pattern can *ever* work sensibly. It seems
it clearly can and does if certain assumptions are met. Assumptions which I
actually know *are* met, in the case in which I potentially wanted to use
it!

One outstanding question I have. Based on a lot of helpful responses given
to the SO question I can now test and see what disk buffers are generated
(by setting `log_temp_files` to `0` and then `tail -f log`), as well as how
long it takes for results to start arriving.

With a large (10,000,000 row) test table, if I do `SELECT * FROM table` on
psql it starts to return results immediately with no disk buffer. If I do
`FETCH ALL FROM cursortotable` on psql it takes about 7.5 seconds to start
returning results, and generates a 14MB buffer. If I do `SELECT * FROM
table` on a correctly coded streaming client, it also starts to return
results immediately with no disk buffer. But if I do `FETCH ALL FROM
cursortotable` from my streaming client, it takes about 1.5 seconds for
results to start coming... but again with no disk buffer, as hoped

I was kind of hoping that the 'it creates a buffer' and the 'it takes a
while to start' issues would be pretty much directly aligned, but it's
clearly not as simple as that! I don't know if you can offer any more
helpful insight on this last aspect?

Many thanks,

Mike

Re: [PERFORM] Number of characters in column preventing index usage

[Hustler DBA]

Thanks you guys are correct... the size of the table caused the optimizer
to do a seq scan instead of using the index. I tried it on a  24 MB and 1
GB table and the expected index was used.



On Fri, Feb 17, 2017 at 7:04 PM, Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Fri, Feb 17, 2017 at 3:49 PM, Tomas Vondra <
> tomas.von...@2ndquadrant.com>
> > wrote:
> >> That may seem a bit strange, but I'd bet it finds the short value in
> some
> >> statistic (MCV, histogram) ans so can provide very accurate estimate.
>
> > ​I'm not seeing how any of the statistic columns would capture a value
> that
> > doesn't actually appear in the table...(actual ... row=0)​
>
> I think it's the other way around.  It found
> '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' in the stats, concluded
> (accurately) that there would be five matches, and on the strength of that
> decided that a seqscan over this very tiny table would be faster than an
> indexscan.  In the other case, the short string exists neither in the
> table nor the stats, and the default estimate is turning out to be that
> there's a single match, for which it likes the indexscan solution.  This
> is all pretty unsurprising if '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'
> is in the most-common-values list.  Anything that's *not* in that list
> is going to get a smaller rowcount estimate.  (I don't think that the
> string length, per se, has anything to do with it.)
>
> I'm not sure what performance problem the OP was looking to solve,
> but expecting experiments on toy-sized tables to give the same plans
> as you get on large tables is a standard mistake when learning to work
> with the PG planner.
>
> Also, if toy-sized tables are all you've got, meaning the whole database
> can be expected to stay RAM-resident at all times, it'd be a good idea
> to reduce random_page_cost to reflect that.  The default planner cost
> settings are meant for data that's mostly on spinning rust.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Number of characters in column preventing index usage

[Tom Lane]

"David G. Johnston"  writes:
> On Fri, Feb 17, 2017 at 3:49 PM, Tomas Vondra 
> wrote:
>> That may seem a bit strange, but I'd bet it finds the short value in some
>> statistic (MCV, histogram) ans so can provide very accurate estimate.

> ​I'm not seeing how any of the statistic columns would capture a value that
> doesn't actually appear in the table...(actual ... row=0)​

I think it's the other way around.  It found
'01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' in the stats, concluded
(accurately) that there would be five matches, and on the strength of that
decided that a seqscan over this very tiny table would be faster than an
indexscan.  In the other case, the short string exists neither in the
table nor the stats, and the default estimate is turning out to be that
there's a single match, for which it likes the indexscan solution.  This
is all pretty unsurprising if '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'
is in the most-common-values list.  Anything that's *not* in that list
is going to get a smaller rowcount estimate.  (I don't think that the
string length, per se, has anything to do with it.)

I'm not sure what performance problem the OP was looking to solve,
but expecting experiments on toy-sized tables to give the same plans
as you get on large tables is a standard mistake when learning to work
with the PG planner.

Also, if toy-sized tables are all you've got, meaning the whole database
can be expected to stay RAM-resident at all times, it'd be a good idea
to reduce random_page_cost to reflect that.  The default planner cost
settings are meant for data that's mostly on spinning rust.

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Number of characters in column preventing index usage

[David G. Johnston]

On Fri, Feb 17, 2017 at 3:49 PM, Tomas Vondra 
wrote:

>  That may seem a bit strange, but I'd bet it finds the short value in some
> statistic (MCV, histogram) ans so can provide very accurate estimate.


​​ ->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29 rows=1
width=0) (actual time=0.043..0.043 rows=0 loops=1)

​I'm not seeing how any of the statistic columns would capture a value that
doesn't actually appear in the table...(actual ... row=0)​

Unless there is some prefix matching going on here since the short value is
a substring(1, n) of the longer one which does appear 5 times.

​I guess maybe because the value doesn't appear it uses the index (via IOS)
to confirm absence (or near absence, i.e., 1) while, knowing the larger
value appears 5 times out of 223, it decides a quick table scan is faster
than any form of double-lookup (whether on the visibility map or the heap).

​https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html​

​David J.​

Re: [PERFORM] Number of characters in column preventing index usage

[Hustler DBA]

Yes, both queries are the same, I just shorten the parameter value to see
what would have happened. The database that I inherited has a column that
stores GUID/UUIDs in a varchar(255) and a select on that table on that
column is doing a FULL TABLE SCAN (seq scan). All the values in the column
are 36 characters long. The table is 104 KB.

I realize that there was no index on that column so when I created the
index and tried to search on a parameter value, it doesn't use the index,
but when I shorten the parameter value then the optimizer decides to use an
index for the search.



On Fri, Feb 17, 2017 at 5:52 PM, Tomas Vondra 
wrote:

> On 02/17/2017 11:42 PM, David G. Johnston wrote:
>
>> On Fri, Feb 17, 2017 at 3:19 PM, Hustler DBA > >wrote:
>>
>>
>>
>> my_db=# create index tab_idx1 on tab(ID);
>>
>> CREATE INDEX
>> my_db=# explain (analyze, buffers) select count(*) from tab where ID
>> = '01625cfa-2bf8-45cf' ;
>>   QUERY
>> PLAN
>> 
>> 
>> ---
>>  Aggregate  (cost=8.29..8.30 rows=1 width=0) (actual
>> time=0.048..0.048 rows=1 loops=1)
>>Buffers: shared read=2
>>->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29
>> rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=1)
>>  Index Cond: (ID = '01625cfa-2bf8-45cf'::text)
>>
>>
>>
>>->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual
>> time=0.031..0.108 rows=5 loops=1)
>>  Filter: ((ID)::text =
>> '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'::text)
>>  Rows Removed by Filter: 218
>>  Buffers: shared hit=12
>>  Planning time: 0.122 ms
>>  Execution time: 0.180 ms
>> (8 rows)
>>
>>
>> ​IIRC the only reason the first query cares to use the index is because
>> it can perform an Index Only Scan and thus avoid touching the heap at
>> all.  If it cannot avoid touching the heap the planner is going to just
>> use a sequential scan to retrieve the records directly from the heap and
>> save the index lookup step.
>>
>>
> I don't follow - the queries are exactly the same in both cases, except
> the parameter value. So both cases are eligible for index only scan.
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Number of characters in column preventing index usage

[Tomas Vondra]

On 02/17/2017 11:42 PM, David G. Johnston wrote:

On Fri, Feb 17, 2017 at 3:19 PM, Hustler DBA >wrote:


my_db=# create index tab_idx1 on tab(ID);

CREATE INDEX
my_db=# explain (analyze, buffers) select count(*) from tab where ID
= '01625cfa-2bf8-45cf' ;
  QUERY
PLAN

---
 Aggregate  (cost=8.29..8.30 rows=1 width=0) (actual
time=0.048..0.048 rows=1 loops=1)
   Buffers: shared read=2
   ->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29
rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=1)
 Index Cond: (ID = '01625cfa-2bf8-45cf'::text)



   ->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual
time=0.031..0.108 rows=5 loops=1)
 Filter: ((ID)::text =
'01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'::text)
 Rows Removed by Filter: 218
 Buffers: shared hit=12
 Planning time: 0.122 ms
 Execution time: 0.180 ms
(8 rows)


​IIRC the only reason the first query cares to use the index is because
it can perform an Index Only Scan and thus avoid touching the heap at
all.  If it cannot avoid touching the heap the planner is going to just
use a sequential scan to retrieve the records directly from the heap and
save the index lookup step.



I don't follow - the queries are exactly the same in both cases, except 
the parameter value. So both cases are eligible for index only scan.


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Number of characters in column preventing index usage

[Alvaro Herrera]

Hustler DBA wrote:
> I am seeing this strange behavior, I don't know if this is by design by
> Postgres.
> 
> I have an index on a column which is defined as "character varying(255)".
> When the value I am searching for is of a certain length, the optimizer
> uses the index but when the value is long, the optimizer doesn't use the
> index but does a seq scan on the table. Is this by design? How can I make
> the optimizer use the index no matter what the size/length of the value
> being searched for?

As I recall, selectivity for strings is estimated based on the length of
the string.  Since your sample string looks suspiciously like an UUID,
perhaps you'd be better served by using an UUID column for it, which may
give better results.  This would prevent you from using the shortened
version for searches (which I suppose you can do with LIKE using the
varchar type), but you could replace it with something like this:

select *
from tab
where ID between '01625cfa-2bf8-45cf--' and
  '01625cfa-2bf8-45cf--';

Storage (both the table and indexes) is going to be more efficient this
way too.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Number of characters in column preventing index usage

[Tomas Vondra]

Hi,

On 02/17/2017 11:19 PM, Hustler DBA wrote:

I am seeing this strange behavior, I don't know if this is by design by
Postgres.

I have an index on a column which is defined as "character
varying(255)". When the value I am searching for is of a certain length,
the optimizer uses the index but when the value is long, the optimizer
doesn't use the index but does a seq scan on the table. Is this by
design? How can I make the optimizer use the index no matter what the
size/length of the value being searched for?



AFAIK there are no such checks, i.e. the optimizer does not consider the 
length of the value when deciding between scan types.




PostgreSQL version: 9.4



That's good to know, but we also need information about the table 
involved in your queries. I'd bet the table is tiny (it seems to be just 
12 pages, so ~100kB), making the indexes rather useless.



my_db=# explain (analyze, buffers) select count(*) from tab where ID =
'01625cfa-2bf8-45cf' ;
  QUERY PLAN

---
 Aggregate  (cost=8.29..8.30 rows=1 width=0) (actual time=0.048..0.048
rows=1 loops=1)
   Buffers: shared read=2
   ->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29 rows=1
width=0) (actual time=0.043..0.043 rows=0 loops=1)
 Index Cond: (ID = '01625cfa-2bf8-45cf'::text)
 Heap Fetches: 0
 Buffers: shared read=2
 Planning time: 0.250 ms
 Execution time: 0.096 ms
(8 rows)

my_db=# explain (analyze, buffers) select count(*) from tab where ID =
'01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' ;
QUERY PLAN

---
 Aggregate  (cost=14.80..14.81 rows=1 width=0) (actual time=0.115..0.115
rows=1 loops=1)
   Buffers: shared hit=12
   ->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual
time=0.031..0.108 rows=5 loops=1)
 Filter: ((ID)::text = '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'::text)
 Rows Removed by Filter: 218
 Buffers: shared hit=12
 Planning time: 0.122 ms
 Execution time: 0.180 ms
(8 rows)


The only difference I see is that for the long value the planner expects 
5 rows, while for the short one it expects 1 row. That may seem a bit 
strange, but I'd bet it finds the short value in some statistic (MCV, 
histogram) ans so can provide very accurate estimate. While for the 
longer one, it ends up using some default (0.5% for equality IIRC) or 
value deduced from ndistinct. Or something like that.


The differences between the two plans are rather negligible, both in 
terms of costs (8.3 vs. 14.81) and runtime (0.1 vs 0.2 ms). The choice 
of a sequential scan seems perfectly reasonable for such tiny tables.


FWIW it's impossible to draw conclusions based on two EXPLAIN ANALYZE 
executions. The timing instrumentation from EXPLAIN ANALYZE may have 
significant impact impact (different for each plan!). You also need to 
testing with more values and longer runs, not just a single execution 
(there are caching effects etc.)


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Number of characters in column preventing index usage

[David G. Johnston]

On Fri, Feb 17, 2017 at 3:19 PM, Hustler DBA  wrote:

>
> my_db=# create index tab_idx1 on tab(ID);
>
> CREATE INDEX
> my_db=# explain (analyze, buffers) select count(*) from tab where ID = '
> 01625cfa-2bf8-45cf' ;
>   QUERY PLAN
>
> 
> 
> ---
>  Aggregate  (cost=8.29..8.30 rows=1 width=0) (actual time=0.048..0.048
> rows=1 loops=1)
>Buffers: shared read=2
>->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29 rows=1
> width=0) (actual time=0.043..0.043 rows=0 loops=1)
>  Index Cond: (ID = '01625cfa-2bf8-45cf'::text)
>
>

>->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual
> time=0.031..0.108 rows=5 loops=1)
>  Filter: ((ID)::text = '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea
> '::text)
>  Rows Removed by Filter: 218
>  Buffers: shared hit=12
>  Planning time: 0.122 ms
>  Execution time: 0.180 ms
> (8 rows)
>

​IIRC the only reason the first query cares to use the index is because it
can perform an Index Only Scan and thus avoid touching the heap at all.  If
it cannot avoid touching the heap the planner is going to just use a
sequential scan to retrieve the records directly from the heap and save the
index lookup step.

David J.

[PERFORM] Number of characters in column preventing index usage

[Hustler DBA]

I am seeing this strange behavior, I don't know if this is by design by
Postgres.

I have an index on a column which is defined as "character varying(255)".
When the value I am searching for is of a certain length, the optimizer
uses the index but when the value is long, the optimizer doesn't use the
index but does a seq scan on the table. Is this by design? How can I make
the optimizer use the index no matter what the size/length of the value
being searched for?


PostgreSQL version: 9.4


my_db=# explain (analyze, buffers) select count(*) from tab where ID =
'01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' ;
QUERY PLAN

---
 Aggregate  (cost=14.80..14.81 rows=1 width=0) (actual time=0.114..0.114
rows=1 loops=1)
   Buffers: shared hit=12
   ->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual
time=0.025..0.109 rows=5 loops=1)
 Filter: ((ID)::text = '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'::text)
 Rows Removed by Filter: 218
 Buffers: shared hit=12
 Planning time: 0.155 ms
 Execution time: 0.167 ms
(8 rows)

my_db=# create index tab_idx1 on tab(ID);

CREATE INDEX
my_db=# explain (analyze, buffers) select count(*) from tab where ID = '
01625cfa-2bf8-45cf' ;
  QUERY PLAN

---
 Aggregate  (cost=8.29..8.30 rows=1 width=0) (actual time=0.048..0.048
rows=1 loops=1)
   Buffers: shared read=2
   ->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29 rows=1
width=0) (actual time=0.043..0.043 rows=0 loops=1)
 Index Cond: (ID = '01625cfa-2bf8-45cf'::text)
 Heap Fetches: 0
 Buffers: shared read=2
 Planning time: 0.250 ms
 Execution time: 0.096 ms
(8 rows)

my_db=# explain (analyze, buffers) select count(*) from tab where ID = '
01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' ;
QUERY PLAN

---
 Aggregate  (cost=14.80..14.81 rows=1 width=0) (actual time=0.115..0.115
rows=1 loops=1)
   Buffers: shared hit=12
   ->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual
time=0.031..0.108 rows=5 loops=1)
 Filter: ((ID)::text = '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'::text)
 Rows Removed by Filter: 218
 Buffers: shared hit=12
 Planning time: 0.122 ms
 Execution time: 0.180 ms
(8 rows)

my_db=#

Re: [PERFORM] pgsql connection timeone

[Vucomir Ianculov]

Hi, 

sorry for the late replay, i have check but from what i see this is on the 
application side. 
i'm not able to login to postgres from the command line. 

dose anyone have any other ideas on this problem. 

Br, 
vuko 


- Original Message -

From: "Vitalii Tymchyshyn"  
To: "Vucomir Ianculov"  
Cc: pgsql-performance@postgresql.org, "Tom Lane"  
Sent: Saturday, February 4, 2017 8:38:34 PM 
Subject: Re: [PERFORM] pgsql connection timeone 


Well, you can try switching to urandom, see 
http://stackoverflow.com/questions/137212/how-to-solve-performance-problem-with-java-securerandom
 , second answer. 


сб, 4 лют. 2017 о 11:45 Vucomir Ianculov < vuko...@os-ux.com > пише: 




Hi Vitalii, 

no, hove can i check it? searched but did not find any useful information . 

Thanks, 

Br, 
Vuko 


From: "Vitalii Tymchyshyn" < v...@tym.im > 
To: "Vucomir Ianculov" < vuko...@os-ux.com >, "Tom Lane" < t...@sss.pgh.pa.us > 
Cc: pgsql-performance@postgresql.org 
Sent: Wednesday, February 1, 2017 7:11:12 PM 



Subject: Re: [PERFORM] pgsql connection timeone 

Just a wild guess, but did you check your random source? We had similar 
problems in Oracle and had to switch to /dev/urandom. It can be done with a 
system variable setting. 



On Wed, Feb 1, 2017, 7:52 AM Vucomir Ianculov < vuko...@os-ux.com > wrote: 




can anyone help me with my problem? 
i'm really don't know from when the problem can be. 






From: "Vucomir Ianculov" < vuko...@os-ux.com > 
To: "Tom Lane" < t...@sss.pgh.pa.us > 
Cc: pgsql-performance@postgresql.org 
Sent: Saturday, January 28, 2017 12:03:55 PM 



Subject: Re: [PERFORM] pgsql connection timeone 


Hi Tom, 

this is the entry from pg_hba.conf 

host all all 0.0.0.0/0 md5 

i needed to restart postgres service to be able to accept new connection, witch 
it strange becouse there was no load on the server and it head a lot of free 
ram. 






From: "Tom Lane" < t...@sss.pgh.pa.us > 
To: "Vucomir Ianculov" < vuko...@os-ux.com > 
Cc: pgsql-performance@postgresql.org 
Sent: Wednesday, January 25, 2017 3:15:28 PM 
Subject: Re: [PERFORM] pgsql connection timeone 

Vucomir Ianculov < vuko...@os-ux.com > writes: 
> i'm seeing a lot of connection time out in postgresql log 

> 2017-01-25 11:09:47 EET [6897-1] XXX@YYY FATAL: canceling authentication due 
> to timeout 
> 2017-01-25 11:10:15 EET [6901-1] XXX@YYY FATAL: canceling authentication due 
> to timeout 
> 2017-01-25 11:10:17 EET [6902-1] xxx@YYY FATAL: canceling authentication due 
> to timeout 

So ... what authentication method are you using? 

regards, tom lane 

Re: [PERFORM] Correct use of cursors for very large result sets in Postgres

[Tom Lane]

Mike Beaton  writes:
> [ generally accurate information ]

> **WARNINGS**

> It would *never* make sense to do `FETCH ALL FROM cursor` for
> astronomically large data, if your client side code (including your data
> access layer) has any bottleneck at all at which means that all the data
> from a command is fetched before any processing can be done. Many data
> access layers (and especially data access wrappers) are like this. So
> beware. But it is also true that not all client side code is made this way.

It would probably be good to point out that most client-side libraries
will do it that way, including libpq, because then they can make success
or failure of the query look atomic to the application.  If you use an
API that lets you see rows as they come off the wire, it's up to you
to recover properly from a query failure that occurs after some/many rows
have already been returned.

> Returning huge data using a `TABLE` or `SETOF` return type from within a
> PostgeSQL function will *always* be broken (i.e. will create a huge buffer
> and take a very long time to start). This will be so whether the function
> is called from SQL to SQL or called over the wire.

I believe this is false in general.  I think it's probably true for all
the standard PL languages, because they don't want to bother with
suspending/resuming execution, so they make "RETURN NEXT" add the row to
a tuplestore not return it immediately.  But it's definitely possible to
write a C function that returns a row at a time, and depending on what the
calling SQL statement looks like, that could get streamed back to the
client live rather than being buffered first.

As a trivial example, if you do
select generate_series(1,1);
in psql and watch what's happening with "top", you'll see psql's memory
usage going through the roof (because libpq tries to buffer the result)
but the connected backend's memory usage is steady as a rock --- nor
does it dump the data into a temporary file.  On the other hand,
select * from generate_series(1,1);
does dump the data into a temp file, something we ought to work on
improving.

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Correct use of cursors for very large result sets in Postgres

[Mike Beaton]

I asked the same question at the same time on Stack Overflow (sincere
apologies if this is a breach of etiquette - I really needed an answer, and
I thought the two communities might not overlap).

Stackoverflow now has an answer, by me:
http://stackoverflow.com/q/42292341/#42297234 - which is based on
accumulating the most consistent, coherent information from the answers and
comments given there so far.

I think this is right, and I happily repeat it below, for anyone finding my
question on this list. But I would still *love* to find official PostgreSQL
documentation of all this. And of course to be told - quickly! - if anyone
knows it is still wrong.

***The answer is:***

**Q1:** For `SELECT * FROM AstronomicallyHugeTable` sent over the wire,
then PostgreSQL will *not* generate a huge buffer, and will stream the data
efficiently, starting quickly, to the client.

**Q2:** For `FETCH ALL FROM CursorToAstronomicallyHugeTable` sent over the
wire, then PostgreSQL will also *not* generate a huge buffer, and also will
stream the data efficiently, starting quickly, to the client.

**Implications of this for `FETCH ALL FROM cursor`**

IF (and this is a big if) you have client software which is NOT going to
store all the fetched data anywhere, but is just trying to do something
with it row by row (and this presupposes that your data access layer
supports this, which Npgsql does), then there is nothing wrong with `FETCH
ALL FROM cursor`. No huge buffers anywhere. No long setup time. Processing
huge data this way will certainly run for a very long time - or at least
until the user or some other condition aborts the process, and the cursor
can be closed. But it will start to run quickly, and its usage of resources
will be efficient.

**WARNINGS**

It would *never* make sense to do `FETCH ALL FROM cursor` for
astronomically large data, if your client side code (including your data
access layer) has any bottleneck at all at which means that all the data
from a command is fetched before any processing can be done. Many data
access layers (and especially data access wrappers) are like this. So
beware. But it is also true that not all client side code is made this way.

Returning huge data using a `TABLE` or `SETOF` return type from within a
PostgeSQL function will *always* be broken (i.e. will create a huge buffer
and take a very long time to start). This will be so whether the function
is called from SQL to SQL or called over the wire. The bottleneck is before
the function returns. For efficient returns of very large data sets you
must use a cursor return from a function (or else do `SELECT *` directly
over the wire), in every case.

[PERFORM] Correct use of cursors for very large result sets in Postgres

[Mike Beaton]

**Short version of my question:**

If I hold a cursor reference to an astronomically huge result set in my
client code, would it be ridiculous (i.e. completely defeats the point of
cursors) to issue "FETCH ALL FROM cursorname" as my next command? Or would
this slowly stream the data back to me as I consume it (at least in
principle, assuming that I have a well written driver sitting between me
and Postgres)?

**More detail**

If I understand things at all correctly, then Postgres cursors are REALLY
for dealing with the following problem [even though they can be used
(abused?) for other things, such as returning multiple different result
sets from one function]:

> Note: The current implementation of RETURN NEXT and RETURN QUERY
> stores the entire result set before returning from the function, as
> discussed above. That means that if a PL/pgSQL function produces a
> very large result set, performance might be poor: data will be written
> to disk to avoid memory exhaustion, but the function itself will not
> return until the entire result set has been generated.

(ref:
https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html)

But (again if I understand correctly) when you write a function which
returns a cursor then the whole query is NOT buffered into memory (and
disk) before the user of the function can start to consume anything, but
instead the results can be consumed bit by bit. (There is more overhead
setting up and using the cursor, but it's worth it to avoid massive buffer
allocation for very large result sets.)

(ref:
https://www.postgresql.org/docs/9.6/static/plpgsql-cursors.html#AEN66551)

I would like to understand how this relates to SELECTS and FETCHES over the
wire to a Postgres server.

In all cases, I'm talk about consuming results from client code which is
communicating with Postgres on a socket behind the scenes (using the Npgsql
library in my case, actually).

Q1: What if I try to execute "SELECT * FROM AstronomicallyLargeTable" as my
only command over the wire to Postgres? Will that allocate all the memory
for the entire select and then start to send data back to me? Or will it
(effectively) generate its own cursor and stream the data back a little at
a time (with no huge additional buffer allocation on the server)?

Q2: What if I already have a cursor reference to an astronomically large
result set (say because I've already done one round trip, and got back the
cursor reference from some function), and then I execute "FETCH ALL FROM
cursorname" over the wire to Postgres? Is that stupid, because it will
allocate ALL the memory for all the results *on the Postgres server* before
sending anything back to me? Or will "FETCH ALL FROM cursorname" actually
work as I'd like it to, streaming the data back slowly as I consume it,
without any massive buffer allocation happening on the Postgres server?