pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Consult is very slow
3+ messages / 3 participants
[nested] [flat]

* Consult is very slow
@ 2004-11-12 16:03  Vida Luz <vlal@ideay.net.ni>
  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:33  Jaime Casanova <systemguards@yahoo.com>
  parent: Vida Luz <vlal@ideay.net.ni>
  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 17:09  Joshua D. Drake <jd@commandprompt.com>
  parent: Vida Luz <vlal@ideay.net.ni>
  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