Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow response from 'SELECT * FROM table'

Slow response from 'SELECT * FROM table'

From
Jirka Novak
Date:
Hi,

   I have table with 30 columns and 30000..500000 rows. When I make
'SELECT * FROM table' postgresql start doing something and return first
row after 10s (for 30k rows) and after 5min (500k rows). It looks like
it copy whole response to temp space and after that it shows it.
   I don't know why. I tested same table structure and datas on Oracle
and MSSQL and both returned first row immediatly.
   Have someone any idea?

                            Jirka Novak

Re: Slow response from 'SELECT * FROM table'

From
Jakub Ouhrabka
Date:
hi,

do you really need all 500k records? if not i'd suggest using limit and
offset clause (select * from table order by xy limit 100 - xy should be
indexed...) or if you really need all records use a cursor.

kuba

On Mon, 11 Nov 2002, Jirka Novak wrote:

> Hi,
>
>    I have table with 30 columns and 30000..500000 rows. When I make
> 'SELECT * FROM table' postgresql start doing something and return first
> row after 10s (for 30k rows) and after 5min (500k rows). It looks like
> it copy whole response to temp space and after that it shows it.
>    I don't know why. I tested same table structure and datas on Oracle
> and MSSQL and both returned first row immediatly.
>    Have someone any idea?
>
>                             Jirka Novak
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: Slow response from 'SELECT * FROM table'

From
Wei Weng
Date:
I am curious, what performance difference does it make to use vanilla
SELECT with to use cursor (for retrieving the entire records)?

Thanks

Wei

At 11:13 AM 11/11/2002 +0100, Jakub Ouhrabka wrote:
>hi,
>
>do you really need all 500k records? if not i'd suggest using limit and
>offset clause (select * from table order by xy limit 100 - xy should be
>indexed...) or if you really need all records use a cursor.
>
>kuba
>
>On Mon, 11 Nov 2002, Jirka Novak wrote:
>
> > Hi,
> >
> >    I have table with 30 columns and 30000..500000 rows. When I make
> > 'SELECT * FROM table' postgresql start doing something and return first
> > row after 10s (for 30k rows) and after 5min (500k rows). It looks like
> > it copy whole response to temp space and after that it shows it.
> >    I don't know why. I tested same table structure and datas on Oracle
> > and MSSQL and both returned first row immediatly.
> >    Have someone any idea?
> >
> >                                                       Jirka Novak
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly

Re: Slow response from 'SELECT * FROM table'

From
"Josh Berkus"
Date:
Jirka,

>   I have table with 30 columns and 30000..500000 rows. When I make
> 'SELECT * FROM table' postgresql start doing something and return
> first row after 10s (for 30k rows) and after 5min (500k rows). It
> looks like it copy whole response to temp space and after that it
> shows it.
>   I don't know why. I tested same table structure and datas on Oracle
>
> and MSSQL and both returned first row immediatly.
>   Have someone any idea?

I can think of any number of reasons why.   However, I need more detail
from you:

1) Why are you selecting 500,000 rows at once?

2) Is "SELECT * FROM table_a" the entirety of your query, or was there
more to it than that?

3) Are you talking about PSQL, or some other interface?

-Josh Berkus

Re: Slow response from 'SELECT * FROM table'

From
Andrew Sullivan
Date:
On Mon, Nov 11, 2002 at 12:19:47PM -0500, Wei Weng wrote:
> I am curious, what performance difference does it make to use vanilla
> SELECT with to use cursor (for retrieving the entire records)?

If you use a cursor, you don't need to buffer the entire record set
before returning it.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: Slow response from 'SELECT * FROM table'

From
Jirka Novak
Date:
Thanks all,

   cursor resolved this problem. I thinked that queries are rewriten
into implicit cursor, so I didn't use it for query. Now I see, I was wrong.

                        Jirka Novak