Date: Wed, 19 Nov 2003 11:41:41 +0100
From: Uros <uros@sir-mag.com>
Reply-To: Uros <uros@sir-mag.com>
Message-ID: <81222392078.20031119114141@sir-mag.com>
To: pgsql-general@postgresql.org
Subject: Optimizing query
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.
         
-- 
Best regards,
 Uros                          mailto:uros@sir-mag.com