pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConsult is very slow
3+ messages / 3 participants
[nested] [flat]
* Consult is very slow
@ 2004-11-12 16:03 Vida Luz <vlal@ideay.net.ni>
2004-11-12 16:33 ` Re: Consult is very slow Jaime Casanova <systemguards@yahoo.com>
2004-11-12 17:09 ` Re: Consult is very slow Joshua D. Drake <jd@commandprompt.com>
0 siblings, 2 replies; 3+ messages in thread
From: Vida Luz @ 2004-11-12 16:03 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Consult is very slow
2004-11-12 16:03 Consult is very slow Vida Luz <vlal@ideay.net.ni>
@ 2004-11-12 16:33 ` Jaime Casanova <systemguards@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jaime Casanova @ 2004-11-12 16:33 UTC (permalink / raw)
To: Vida Luz <vlal@ideay.net.ni>; pgsql-admin
--- Vida Luz <vlal@ideay.net.ni> escribió:
> 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.
>
Maybe this question should be done at the PERFORMANCE
list.
What about creating the index on the extract
expresion?
CREATE INDEX tuIndice ON correo (date_part('year',
fecha));
I found the other way i suggested in the spanish list
is not correct (sintax) but this way it works (i have
proved it).
regards,
Jaime Casanova
_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Consult is very slow
2004-11-12 16:03 Consult is very slow Vida Luz <vlal@ideay.net.ni>
@ 2004-11-12 17:09 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 3+ messages in thread
From: Joshua D. Drake @ 2004-11-12 17:09 UTC (permalink / raw)
To: Vida Luz <vlal@ideay.net.ni>; +Cc: pgsql-admin
V
>
> 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?
You could use date_part and create a funtional index.
Sincerely,
Joshua D. Drake
>
> Thanks.
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachments:
jd.vcf (text/x-vcard, 638B) download
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:jd@commandprompt.com
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-12 17:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-12 16:03 Consult is very slow Vida Luz <vlal@ideay.net.ni>
2004-11-12 16:33 ` Jaime Casanova <systemguards@yahoo.com>
2004-11-12 17:09 ` Joshua D. Drake <jd@commandprompt.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