Date: Wed, 09 Apr 2008 20:41:29 +0200
To: "Luigi N. Puleio" <npuleio@rocketmail.com>,
	"Craig Ringer" <craig@postnewspapers.com.au>
Subject: Re: EXPLAIN detail
From: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <981759.47577.qm@web33504.mail.mud.yahoo.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t9cwvfgecigqcu@apollo13.peufeu.com>
In-Reply-To: <981759.47577.qm@web33504.mail.mud.yahoo.com>
User-Agent: Opera Mail/9.24 (Linux)


> Well, this table has a primary key index on first column called acctid=
  =

> which is an integer; instead the calldate column is a TIMESTAMPTZ and =
in  =

> fact I'm using to do (calldate)::date in the ON clause because since t=
he  =

> time part of that column is always different and in the nesting I have=
  =

> to identificate the date is the same...
>
> the other two columns (src and lastdata) are both VARCHAR(80) and the =
 =

> query is this one:

	Tip for getting answers from this list :
	You should just post the output of "\d yourtable" from psql, it would b=
e  =

quicker than writing a paragraph... Be lazy, lol.

	So, basically if I understand you are doing a self-join on your table, =
 =

you want all rows from the same day, and you're doing something with the=
  =

dates, and...

	Tip for getting answers from this list :
	Explain (in english) what your query actually does, someone might come =
up  =

with a better idea on HOW to do it.

	Snip :

> EXPLAIN ANALYZE
> SELECT
>       (a.calldate)::date,
>       a.src,
>       a.dst,
>       MIN(e.calldate) - a.calldate
> FROM
>     cdr a
>     INNER JOIN cdr e
>     ON ((e.calldate)::date =3D (a.calldate)::date AND e.src =3D a.src
>         AND e.lastdata =3D '/dati/ita/logoutok' AND e.calldate >=3D  =

> a.calldate)
> WHERE
>      (a.calldate)::date =3D '2008-04-09'
>      AND a.src =3D '410'
>      AND substr(a.dst, 1, 4) =3D '*100'
>      AND a.lastdata =3D '/dati/ita/loginok'
> GROUP BY
>       a.calldate, a.src, a.dst

	OK, I assume you have an index on calldate, which is a TIMESTAMPTZ ?
	(in that case, why is it called calldate, and not calltimestamp ?...)

	Bad news, the index is useless for this condition :
	(a.calldate)::date =3D '2008-04-09'
	There, you are asking postgres to scan the entire table, convert the  =

column to date, and test. Bad.

	In order to use the index, you could rewrite it as something like :
	a.calldate >=3D '2008-04-09' AND a.calldate < ('2008-04-09'::DATE + '1 =
 =

DAY'::INTERVAL)
	This is a RANGE query (just like BETWEEN) which is index-friendly.

	Personnaly, I wouldn't do it that way : since you use the date (and not=
  =

the time, I presume you only use the time for display purposes) I would =
 =

just store the timestamptz in "calltimestamp" and the date in "calldate"=
,  =

with a trigger to ensure the date is set to calltimestamp::date every ti=
me  =

a row is inserted/updated.
	This is better than a function index since you use that column a lot in=
  =

your query, it will be slightly faster, and it will save a lot of  =

timestamptz->date casts hence it will save CPU cycles

	Try this last option (separate date column), and repost EXPLAIN ANALYZE=
  =

of your query so it can be optimized further.

	Also, PLEASE don't use substr(), use a.dst LIKE '*100%', look in the  =

manual. LIKE 'foo%' is indexable if you create the proper index.