pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT * FROM table is too slow
7+ messages / 6 participants
[nested] [flat]
* SELECT * FROM table is too slow
@ 2007-12-16 18:34 Adam PAPAI <wooh@wooh.hu>
2007-12-16 18:37 ` Re: SELECT * FROM table is too slow Joshua D. Drake <jd@commandprompt.com>
2007-12-16 19:03 ` Re: SELECT * FROM table is too slow Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 2 replies; 7+ messages in thread
From: Adam PAPAI @ 2007-12-16 18:34 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM table is too slow
2007-12-16 18:34 SELECT * FROM table is too slow Adam PAPAI <wooh@wooh.hu>
@ 2007-12-16 18:37 ` Joshua D. Drake <jd@commandprompt.com>
2008-01-22 19:44 ` Re: SELECT * FROM table is too slow Luiz K. Matsumura <luiz@planit.com.br>
1 sibling, 1 reply; 7+ messages in thread
From: Joshua D. Drake @ 2007-12-16 18:37 UTC (permalink / raw)
To: Adam PAPAI <wooh@wooh.hu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM table is too slow
2007-12-16 18:34 SELECT * FROM table is too slow Adam PAPAI <wooh@wooh.hu>
2007-12-16 18:37 ` Re: SELECT * FROM table is too slow Joshua D. Drake <jd@commandprompt.com>
@ 2008-01-22 19:44 ` Luiz K. Matsumura <luiz@planit.com.br>
2008-01-22 20:19 ` Re: SELECT * FROM table is too slow Joshua D. Drake <jd@commandprompt.com>
2008-01-23 08:47 ` Re: SELECT * FROM table is too slow Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 2 replies; 7+ messages in thread
From: Luiz K. Matsumura @ 2008-01-22 19:44 UTC (permalink / raw)
To: pgsql-performance
> 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 ?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM table is too slow
2007-12-16 18:34 SELECT * FROM table is too slow Adam PAPAI <wooh@wooh.hu>
2007-12-16 18:37 ` Re: SELECT * FROM table is too slow Joshua D. Drake <jd@commandprompt.com>
2008-01-22 19:44 ` Re: SELECT * FROM table is too slow Luiz K. Matsumura <luiz@planit.com.br>
@ 2008-01-22 20:19 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 7+ messages in thread
From: Joshua D. Drake @ 2008-01-22 20:19 UTC (permalink / raw)
To: Luiz K. Matsumura <luiz@planit.com.br>; +Cc: pgsql-performance
-----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-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM table is too slow
2007-12-16 18:34 SELECT * FROM table is too slow Adam PAPAI <wooh@wooh.hu>
2007-12-16 18:37 ` Re: SELECT * FROM table is too slow Joshua D. Drake <jd@commandprompt.com>
2008-01-22 19:44 ` Re: SELECT * FROM table is too slow Luiz K. Matsumura <luiz@planit.com.br>
@ 2008-01-23 08:47 ` Guillaume Cottenceau <gc@mnc.ch>
1 sibling, 0 replies; 7+ messages in thread
From: Guillaume Cottenceau @ 2008-01-23 08:47 UTC (permalink / raw)
To: Luiz K. Matsumura <luiz@planit.com.br>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM table is too slow
2007-12-16 18:34 SELECT * FROM table is too slow Adam PAPAI <wooh@wooh.hu>
@ 2007-12-16 19:03 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 0 replies; 7+ messages in thread
From: Steinar H. Gunderson @ 2007-12-16 19:03 UTC (permalink / raw)
To: Adam PAPAI <wooh@wooh.hu>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM table is too slow
@ 2007-12-16 22:49 andrew@pillette.com
0 siblings, 0 replies; 7+ messages in thread
From: andrew@pillette.com @ 2007-12-16 22:49 UTC (permalink / raw)
To: Adam PAPAI <wooh@wooh.hu>; +Cc: pgsql-performance
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 the most frequently used index.
Two: did you accidentally put the database on your floppy drive?
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-01-23 08:47 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-16 18:34 SELECT * FROM table is too slow Adam PAPAI <wooh@wooh.hu>
2007-12-16 18:37 ` Joshua D. Drake <jd@commandprompt.com>
2008-01-22 19:44 ` Luiz K. Matsumura <luiz@planit.com.br>
2008-01-22 20:19 ` Joshua D. Drake <jd@commandprompt.com>
2008-01-23 08:47 ` Guillaume Cottenceau <gc@mnc.ch>
2007-12-16 19:03 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-12-16 22:49 Re: SELECT * FROM table is too slow andrew@pillette.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox