Consult is very slow

From: Vida Luz <vlal(at)ideay(dot)net(dot)ni>
To: pgsql-admin(at)postgresql(dot)org
Subject: Consult is very slow
Date: 2004-11-12 16:03:36
Message-ID: Pine.LNX.4.44.0411120952280.11525-100000@ns.ideay.net.ni
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all

I have a table in y database that have 8,000,000 of rows, when I execut a
query on this table, the answuer is very slow.

I have a index in this table by datem, my table is

Column | Type | Modifiers
---------+---------------------------+-----------
nombre | character varying(15) |
mensaje | character varying(250) |
nombre_env | character varying(100) |
cel_env | character varying(15) |
fecha | date |
hora | time(0) without time zone |
Indexes: correo_fecha

Myindex is:
Index "correo_fecha"
Column | Type
--------+------
fecha | date
btree

My query is

select EXTRACT(MONTH FROM M.fecha) as ho,count(*) as tot from correo M
where EXTRACT(YEAR FROM M.fecha)='2004' group by EXTRACT(MONTH FROM
M.fecha);

When I executed a EXPLAIN ANALIZE, I hace the following Answer

Aggregate (cost=122439.31..122558.36 rows=2381 width=4) (actual
time=64626.46..76021.93 rows=11 loops=1)
-> Group (cost=122439.31..122498.84 rows=23809 width=4) (actual
time=63951.10..73332.27 rows=4177209 loops=1)
-> Sort (cost=122439.31..122439.31 rows=23809 width=4) (actual
time=63951.09..67240.94 rows=4177209 loops=1)
-> Seq Scan on web_sms m (cost=0.00..120708.48 rows=23809
width=4) (actual time=0.30..55077.31 rows=4177209 loops=1)
Total runtime: 76069.22 msec

How can I do to acceletate the answer?

Thanks.

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jaime Casanova 2004-11-12 16:33:12 Re: Consult is very slow
Previous Message Andrew Sullivan 2004-11-12 15:57:28 Re: Large transaction problem