X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A0DAAD1D80E for ; Wed, 19 Nov 2003 12:26:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 64236-05 for ; Wed, 19 Nov 2003 08:26:01 -0400 (AST) Received: from citka.mg-proline.si (citka.sir-mag.com [212.18.32.57]) by svr1.postgresql.org (Postfix) with ESMTP id B7EEFD1D838 for ; Wed, 19 Nov 2003 08:25:58 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by citka.mg-proline.si (Postfix) with ESMTP id 7D94770613 for ; Wed, 19 Nov 2003 13:26:01 +0100 (CET) Received: from citka.mg-proline.si ([127.0.0.1]) by localhost (citka.mg-proline.si [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16509-08 for ; Wed, 19 Nov 2003 13:25:57 +0100 (CET) Received: from uros (BSN-77-34-194.dsl.siol.net [193.77.34.194]) by citka.mg-proline.si (Postfix) with ESMTP id 469997060F for ; Wed, 19 Nov 2003 13:25:57 +0100 (CET) Date: Wed, 19 Nov 2003 13:25:46 +0100 From: Uros Reply-To: Uros X-Priority: 3 (Normal) Message-ID: <1121228637937.20031119132546@sir-mag.com> To: pgsql-general@postgresql.org Subject: Re: Optimizing query In-Reply-To: <3FBB60BE.4010407@myrealbox.com> References: <81222392078.20031119114141@sir-mag.com> <3FBB60BE.4010407@myrealbox.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mg-proline.si X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/988 X-Sequence-Number: 52638 Hello Shridhar, I use Matthew's solution and it works. Query takes only half a second. I didn't know that i can index function to. Thanks Uros Wednesday, November 19, 2003, 1:23:26 PM, you wrote: SD> Uros wrote: >> Hello! >> >> I have some trouble getting good results from my query. >> >> here is structure >> >> stat_views >> id | integer >> id_zone | integer >> created | timestamp >> >> >> I have btree index on created and also id and there is 1633832 records in >> that table >> >> First of all I have to manualy set seq_scan to OFF because I always get >> seq_scan. When i set it to off my explain show: >> >> explain SELECT count(*) as views FROM stat_views WHERE id = 12; >> QUERY PLAN >> ---------------------------------------------------------------------------------------------------- >> Aggregate (cost=122734.86..122734.86 rows=1 width=0) >> -> Index Scan using stat_views_id_idx on stat_views >> (cost=0.00..122632.60 rows=40904 width=0) >> Index Cond: (id = 12) >> >> But what I need is to count views for some day, so I use >> >> explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18; >> >> QUERY PLAN >> ------------------------------------------------------------------------------------ >> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0) >> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0) >> Filter: (date_part('day'::text, created) = 18::double precision) >> >> >> How can I make this to use index and speed the query. Now it takes about 12 >> seconds. SD> Can you post explain analyze for the same? SD> Shridhar