Re: Extremely slow performance with 'select *' after insert

Collin Peters wrote:
> The table in question is a simple users table. The details are at the
> bottom of this message. The performance on this table was fine during
> testing with less than 100 users. Then we inserted about 37,000 records
> into the table. Now a 'SELECT * FROM pp_users' takes over 40 seconds!!.
> 37,000 records is not much at all so I am wondering why the slow
> execution time. Here are some stats and log output files.
>
> 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.
> On Internet connection (using pgadmin):
> Total query runtime: 32703 ms.
> Data retrieval runtime: 16109 ms.
> 37326 rows retrieved.
> On db server using psql (somewhat better but still slow for 37000 rows):
> devel=# select * from pp_users;
> Time: 912.779 ms
>
> 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)
>
> Anybody know what would cause things to be so slow? Seems kind of
> absurd really. Indexes shouldn't play a role since a 'select *' does a
> sequential scan. Even so there will be an index on the primary key
> (user_id) which is proved with the query:
> EXPLAIN ANALYZE SELECT * FROM pp_users WHERE user_id < 100
> "Index Scan using pp_users_pkey on pp_users (cost=0.00..7.80 rows=4
> width=1102) (actual time=0.080..0.246 rows=54 loops=1)"
> " Index Cond: (user_id < 100)"
>
> Let me know if any more information would help. This is postgresql
> 7.4.7 (also a unicode database).
>
> Regards,
> Collin

Is that the time spent displaying the data on the screen etc?

How long does this take:
select count(*) from (SELECT * FROM pp_users) as t;

Also, IIRC, this topic may have come up a few weeks ago about timings being off
from pgadmin.

--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________