Re: Optimizing query - Mailing list pgsql-general

From Matthew Lunnon
Subject Re: Optimizing query
Date
Msg-id 003201c3ae92$6d150c20$8e8bbd3e@rwanet.co.uk
Whole thread Raw
In response to Optimizing query  (Uros <uros@sir-mag.com>)
List pgsql-general
Do something like:
 
CREATE OR REPLACE FUNCTION my_date_part( timestamp) RETURNS DOUBLE precision AS '
DECLARE
 mydate ALIAS FOR $1;
BEGIN
 return date_part( ''day'', mydate );
END;' LANGUAGE 'plpgsql' IMMUTABLE;
create index idx_tmp on stat_views( my_date_part( created ) );
or add an extra date_part column to your table which pre-calculates date_part('day', created) and put an index on this.
 
Cheers
Matthew
--
 
----- Original Message -----
From: Uros
Sent: Wednesday, November 19, 2003 10:41 AM
Subject: [GENERAL] Optimizing query

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

_____________________________________________________________________
This e-mail has been scanned for viruses by MCI's Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.mci.com

pgsql-general by date:

Previous
From: "Ben-Nes Michael"
Date:
Subject: defferable update & unique
Next
From: Peter Eisentraut
Date:
Subject: Re: Optimizing query