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 2D818D1D83B for ; Wed, 19 Nov 2003 11:44:25 +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 51967-07 for ; Wed, 19 Nov 2003 07:43:55 -0400 (AST) Received: from mail-relay.rwa-net.co.uk (mail-relay.rwa-net.co.uk [62.189.139.18]) by svr1.postgresql.org (Postfix) with ESMTP id 957AED1B57F for ; Wed, 19 Nov 2003 07:43:33 -0400 (AST) Received: from saturn.rwa-net.co.uk (Saturn.rwa-net.co.uk [62.189.139.33]) by mail-relay.rwa-net.co.uk (8.11.6/8.11.6) with ESMTP id hAJBqOm08854 for ; Wed, 19 Nov 2003 11:52:24 GMT Received: from [62.189.139.142] by saturn.cvw.org.uk (NTMail 7.00.0018/NT4698.00.83343e8d) with ESMTP id gncfbaaa for pgsql-general@postgresql.org; Wed, 19 Nov 2003 11:44:01 +0000 Message-ID: <003201c3ae92$6d150c20$8e8bbd3e@rwanet.co.uk> From: "Matthew Lunnon" To: "Uros" , References: <81222392078.20031119114141@sir-mag.com> Subject: Re: Optimizing query Date: Wed, 19 Nov 2003 11:44:01 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002F_01C3AE92.6D044340" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4922.1500 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=ASCII_FORM_ENTRY, BAYES_30, HTML_30_40, REFERENCES X-Spam-Level: X-Archive-Number: 200311/985 X-Sequence-Number: 52635 This is a multi-part message in MIME format. ------=_NextPart_000_002F_01C3AE92.6D044340 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Do something like: CREATE OR REPLACE FUNCTION my_date_part( timestamp) RETURNS DOUBLE precisio= n 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_pa= rt('day', created) and put an index on this. Cheers Matthew -- ----- Original Message -----=20 From: Uros=20 To: pgsql-general@postgresql.org=20 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 =3D 12; QUERY PLAN -------------------------------------------------------------------------= --------------------------- Aggregate (cost=3D122734.86..122734.86 rows=3D1 width=3D0) -> Index Scan using stat_views_id_idx on stat_views (cost=3D0.00..12= 2632.60 rows=3D40904 width=3D0) Index Cond: (id =3D 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', c= reated) =3D 18; QUERY PLAN -------------------------------------------------------------------------= ----------- Aggregate (cost=3D100101618.08..100101618.08 rows=3D1 width=3D0) -> Seq Scan on stat_views (cost=3D100000000.00..100101565.62 rows=3D= 20984 width=3D0) Filter: (date_part('day'::text, created) =3D 18::double precisio= n) How can I make this to use index and speed the query. Now it takes about = 12 seconds. =20=20=20=20=20=20=20=20=20=20=20 --=20 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 Scanni= ng Services - powered by MessageLabs. For further information visit http://= www.mci.com ------=_NextPart_000_002F_01C3AE92.6D044340 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Do something like:
 
CREATE OR REPLACE FUNCTION my_date_part( t= imestamp)=20 RETURNS DOUBLE precision AS '
DECLARE
 mydate ALIAS FOR=20 $1;
BEGIN
 return date_part( ''day'', mydate );
END;' LANGUAG= E=20 'plpgsql' IMMUTABLE;
create index idx_tmp on stat_views( my_date_part( created ) );
or add an extra date_part column to your t= able=20 which pre-calculates date_part('day= ',=20 created) and put an index on this.
 
Cheers
Matthew
--
 
----- Original Message -----
Fro= m:=20 Uros
Sent: Wednesday, November 19, 2003= 10:41=20 AM
Subject: [GENERAL] Optimizing quer= y

Hello!

I have some trouble getting good results fro= m my=20 query.

here is=20 structure

stat_views
id      &nbs= p; |=20 integer
id_zone   | integer
created   |=20 timestamp


I have btree index on created and also id and there= =20 is  1633832 records in
that table

First of all I have to= =20 manualy set seq_scan to OFF because I always get
seq_scan. When i set = it to=20 off my explain show:

explain SELECT count(*) as views FROM stat_vi= ews=20 WHERE id =3D=20 12;
           =             &nb= sp;            =         =20 QUERY=20 PLAN
-----------------------------------------------------------------= -----------------------------------
 Aggregate =20 (cost=3D122734.86..122734.86 rows=3D1 width=3D0)
   ->&nb= sp; Index=20 Scan using stat_views_id_idx on stat_views  (cost=3D0.00..122632.60= =20 rows=3D40904 width=3D0)
       &nbs= p; Index=20 Cond: (id =3D 12)

But what I need is to count views for some day, = so I=20 use

explain SELECT count(*) as views FROM stat_views WHERE=20 date_part('day', created) =3D=20 18;

          &n= bsp;            = ;            &n= bsp;=20 QUERY=20 PLAN
-----------------------------------------------------------------= -------------------
 Aggregate =20 (cost=3D100101618.08..100101618.08 rows=3D1 width=3D0)
   -&= gt; =20 Seq Scan on stat_views  (cost=3D100000000.00..100101565.62 rows=3D20= 984=20 width=3D0)
         Filter:=20 (date_part('day'::text, created) =3D 18::double precision)


How= can I=20 make this to use index and speed the query. Now it takes about=20 12
seconds.
        
--= =20
Best=20 regards,
 Uros        &nb= sp;            =     =20 mailto:uros@sir-mag.com


-= --------------------------(end=20 of broadcast)---------------------------
TIP 1: subscribe and unsubscr= ibe=20 commands go to majordomo@postgresql.org
=
_____________________________________________________________________This=20 e-mail has been scanned for viruses by MCI's Internet Managed Scanning=20 Services - powered by MessageLabs. For further information visit http://www.mci.com
------=_NextPart_000_002F_01C3AE92.6D044340--