Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.
_______________________________

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux