Обсуждение: Effective limit on size of text type?

Using pg 7.2.1 on OsX 10.1.5

I'm trying dumping encoded email attachments into a text field. Inserts complete quickly enough that I don't notice  a
performancehit (~1 sec).  

However,

test=# \x
test=# select _incomingEnclosures from dl_event where _idNum=3010;

Takes at least a couple of minutes, either through psql or a libpq based connection.

test=# select char_length(_incomingEnclosures) from dl_event where _donorNum=804 and _responseNum=3010;
-[ RECORD 1 ]-------
char_length | 952478

Is nearly immediate.

Then again, without the \x view, I get the header of the column almost instantly, and the rest does scroll by my
console.(perhaps cached?) 

Is this an issue with the frontend or the backend?  Can I work around this by using a different data type?  (I'm not
surethat large objects are well supported in my client library) Maybe bina? 

eric

eric soroos <eric-psql@soroos.net> writes:
> test=# select _incomingEnclosures from dl_event where _idNum=3010;
> Takes at least a couple of minutes, either through psql or a libpq based connection.
> test=# select char_length(_incomingEnclosures) from dl_event where _donorNum=804 and _responseNum=3010;
> -[ RECORD 1 ]-------
> char_length | 952478
> Is nearly immediate.

Given that those queries aren't using comparable WHERE conditions, I'd
wonder about the query plan myself, not the size of the returned data
item.  Do you have an index on _idNum?  Is it getting used?

In a quick test here, selecting a 1MB text value into a file, using

regression=# select f1 from foo
regression-# \g tmpfile

took about a tenth of a second...

            regards, tom lane

On Wed, 07 Aug 2002 22:30:02 -0400 in message <28712.1028773802@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> eric soroos <eric-psql@soroos.net> writes:
> > test=# select _incomingEnclosures from dl_event where _idNum=3010;
> > Takes at least a couple of minutes, either through psql or a libpq based connection.
> > test=# select char_length(_incomingEnclosures) from dl_event where _donorNum=804 and _responseNum=3010;
> > -[ RECORD 1 ]-------
> > char_length | 952478
> > Is nearly immediate.
>
> Given that those queries aren't using comparable WHERE conditions, I'd
> wonder about the query plan myself, not the size of the returned data
> item.  Do you have an index on _idNum?  Is it getting used?

Sorry, that is my fault. I was trying to simplify the conditions.  And in doing so, I created a red herring. Both
querieswere on _donorNum/_responseNum, which together form a unique indexed key. 

test-# explain select _incomingEnclosures from dl_event where _donorNum='6' and _responseNum='3549';
NOTICE:  QUERY PLAN:

Index Scan using dl_event_rnumidx on dl_event  (cost=0.00..6.46 rows=1 width=536)

> In a quick test here, selecting a 1MB text value into a file, using
>
> regression=# select f1 from foo
> regression-# \g tmpfile
>
> took about a tenth of a second...

Ok, tried that and it was about that fast with extended view off. With extended view on, it churns for a while before I
giveup.  

So it appears that this is an artifact of the front end, since the same query with different front end settings has
drasticallydifferent performance.   

And under a little further investigation, it appears that is a similar 'run at the proper speed' flag somewhere in the
libraryI'm using since testing this morning is running at a reasonable (~1sec) speed. 

eric