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 -----
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:[EMAIL PROTECTED]
---------------------------(end
of broadcast)--------------------------- TIP 1: subscribe and unsubscribe
commands go to [EMAIL PROTECTED]
_____________________________________________________________________ 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
|