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)


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

> 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 ?


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings