Thread: SELECT * FROM table is too slow
Hi pgsql-performance,
I've a problem with the select * on a small table.
See below:
x7=# EXPLAIN ANALYZE select * from megjelenesek;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on megjelenesek (cost=0.00..15633.07 rows=207 width=52)
(actual time=103.258..18802.530 rows=162 loops=1)
Total runtime: 18815.362 ms
(2 rows)
x7=# \d megjelenesek;
Table "public.megjelenesek"
Column | Type |
Modifiers
-------------+-----------------------------+------------------------------------------------------------
idn | integer | not null default
nextval('megjelenesek_idn_seq'::regclass)
tag_id | integer |
tag_wlap_id | integer |
get_date | timestamp without time zone | default now()
megjelent | numeric | default 0
Indexes:
"megjelenesek_pkey" PRIMARY KEY, btree (idn)
"megjelenesek_tag_id" hash (tag_id)
"megjelenesek_tag_wlap_id" hash (tag_wlap_id)
x7=# SELECT count(idn) from megjelenesek;
count
-------
162
(1 row)
Why does it take cca 18-20 sec to get the results?
Too many indexes?
--
Adam PAPAI
D i g i t a l Influence
http://www.wooh.hu
E-mail: wooh@wooh.hu
Phone: +36 30 33-55-735 (Hungary)
Adam PAPAI wrote: > Hi pgsql-performance, > > I've a problem with the select * on a small table. > > See below: > > > x7=# SELECT count(idn) from megjelenesek; > count > ------- > 162 > (1 row) > > Why does it take cca 18-20 sec to get the results? > Too many indexes? You likely have a huge amount of dead rows. Try dumping and restoring the table and remember to run vacuum (or autovacuum) often. Sincerely, Joshua D. Drake
On Sun, Dec 16, 2007 at 07:34:45PM +0100, Adam PAPAI wrote: > Why does it take cca 18-20 sec to get the results? > Too many indexes? You cannot possibly have VACUUMed in a long time. Try a VACUUM FULL, and then schedule regular VACUUMs (or use autovacuum). /* Steinar */ -- Homepage: http://www.sesse.net/
Adam PAPAI <wooh@wooh.hu> wrote .. > Hi pgsql-performance, > > I've a problem with the select * on a small table. > I can think of two possibilities for such incredibly slow performance. One: your table has not been VACUUMed for a long time and is full of dead tuples. Try VACUUM FULL on it, or CLUSTER on themost frequently used index. Two: did you accidentally put the database on your floppy drive?
> Adam PAPAI wrote: >> Hi pgsql-performance, >> >> I've a problem with the select * on a small table. >> >> See below: >> >> >> x7=# SELECT count(idn) from megjelenesek; >> count >> ------- >> 162 >> (1 row) >> >> Why does it take cca 18-20 sec to get the results? >> Too many indexes? > > You likely have a huge amount of dead rows. Try dumping and restoring > the table and remember to run vacuum (or autovacuum) often. > > Sincerely, > > Joshua D. Drake wrote:Hi, If we run the commands "vacumm full analyze" and "reindex table", this can be considered as equivalent to making a dump / restore in this case ?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 22 Jan 2008 17:44:33 -0200 "Luiz K. Matsumura" <luiz@planit.com.br> wrote: > > > > Joshua D. Drake wrote:Hi, > > If we run the commands "vacumm full analyze" and "reindex table", > this can be considered as equivalent to making a dump / restore in > this case ? Yes. Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHlk/LATb/zqfZUUQRAua0AKCsZrWrkf0d+jKUa9KK3aTqzuZTZACbBiD5 z3aIswcgRSwywxlhD+dgSHE= =vdeQ -----END PGP SIGNATURE-----
"Luiz K. Matsumura" <luiz 'at' planit.com.br> writes: > If we run the commands "vacumm full analyze" If you're using the cost based vacuum delay, don't forget that it will probably take long; possibly, you may deactivate it locally before running VACUUM FULL, in case the locked table is mandatory for your running application(s). -- Guillaume Cottenceau, MNC Mobile News Channel SA