X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C668E3A4744 for ; Fri, 12 Nov 2004 17:09:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71170-09 for ; Fri, 12 Nov 2004 17:09:45 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id 7E65C3A3F3C for ; Fri, 12 Nov 2004 17:09:45 +0000 (GMT) Received: from [192.168.1.51] (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id iACH9h811483; Fri, 12 Nov 2004 09:09:43 -0800 Message-ID: <4194EE50.6060309@commandprompt.com> Date: Fri, 12 Nov 2004 09:09:36 -0800 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Vida Luz Cc: pgsql-admin@postgresql.org Subject: Re: Consult is very slow References: In-Reply-To: Content-Type: multipart/mixed; boundary="------------060201060202070609070609" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/108 X-Sequence-Number: 15493 This is a multi-part message in MIME format. --------------060201060202070609070609 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------060201060202070609070609 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" 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 --------------060201060202070609070609--