Re: Extremely slow performance with 'select *' after insert of 37, 000 records

I seem to have a problem CC: the list these days...

--- Collin Peters <cpeters(at)mcrt(dot)ca> wrote:
>
> Running the query 'EXPLAIN ANALYZE SELECT * FROM pp_users'
> -----------------------------------------------------------
> "Seq Scan on pp_users (cost=0.00..1597.26 rows=37326 width=1102)
> (actual time=0.029..33.043 rows=37326 loops=1)"
> "Total runtime: 44.344 ms"
> (same stats when run on all computers (lan/internet/localhost)

If I'm reading this correctly each row in pp_users is 1102 bytes wide
(on average) and you are returning 37k rows. That'd be in the
neighborhood of 40 megabytes worth of data.

>
> Running the query 'SELECT * FROM pp_users'
> ------------------------------------------
> On LAN connection (using pgadmin):
> Total query runtime: 14547 ms.
> Data retrieval runtime: 10453 ms.
> 37326 rows retrieved.

40 megs of data transfered in 14.5 seconds works out to be 2.75 megs of
data per second. A little on the slow side but not entirely
unreasonable for a LAN connection. Is your link saturated or running
on cheap NIC hardware? What speeds do you get when you try and ftp a
large file to/from the host? What is the lantency between the client
and server hosts?

> On Internet connection (using pgadmin):
> Total query runtime: 32703 ms.
> Data retrieval runtime: 16109 ms.
> 37326 rows retrieved.

Somewhere around 1.2 megs of data per second. Once again that is not
an unreasonable transfer rate for a WAN. Is your link saturated or
running on cheap NIC hardware? What speeds do you get when you try and
ftp a large file to/from the host? What is the latency between the
client and server hosts?

> On db server using psql (somewhat better but still slow for 37000
> rows):
> devel=# select * from pp_users;
> Time: 912.779 ms

This is telling me your local interface returned 40 megs of data from
the backend to the client in under a second (perhaps disk I/O is the
bottleneck here). Once again, that doesn't sound like an unreasonable
figure to me.

What is your OS/hardware/etc?

Regards,

Shelby Cain

__________________________________
Discover Yahoo!
Use Yahoo! to plan a weekend, have fun online and more. Check it out!
http://discover.yahoo.com/