Обсуждение: Extremely slow performance with 'select *' after insert of 37,000 records
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 --------------------- -- Table: pp_users -- DROP TABLE pp_users; CREATE TABLE pp_users ( user_id serial NOT NULL, title varchar(10), firstname varchar(40) NOT NULL, lastname varchar(40) NOT NULL, shortname varchar(20) NOT NULL, username varchar(20), "password" varchar(40) NOT NULL, birthdate date, sex char(1), weight int4, company varchar(40), email varchar(60), tel1 varchar(40), tel2 varchar(40), fax varchar(40), street varchar(40), city varchar(40), zipcode varchar(10), state varchar(30), country varchar(40), "language" varchar(5), cellphone varchar(40), userstatus_id int4 NOT NULL DEFAULT 1, acc_deleteme varchar(4), proxy text, settings text, creationdate date DEFAULT now(), createdby_user_id int4, div1 varchar(40), div2 varchar(40), div3 varchar(40), div4 varchar(40), role_id int4, joined_date date DEFAULT now(), membership_id varchar(40), password_status int4, CONSTRAINT pp_users_pkey PRIMARY KEY (user_id), CONSTRAINT "$3" FOREIGN KEY (userstatus_id) REFERENCES userstatuses (userstatus_id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT role_id_fk FOREIGN KEY (role_id) REFERENCES pp_roles (role_id) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITH OIDS;
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. _______________________________
I seem to have a problem CC: the list these days... --- Collin Peters <cpeters@mcrt.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/