> Well, this table has a primary key index on first column called
acctid=20=
=20
> which is an integer; instead the calldate column is a TIMESTAMPTZ and
in=
=20=20
> fact I'm using to do (calldate)::date in the ON clause because since
the=
=20=20
> time part of that column is always different and in the nesting I
have=20=
=20
> to identificate the date is the same...
>
> the other two columns (src and lastdata) are both VARCHAR(80) and
the=20=
=20
> query is this one:
Tip for getting answers from this list :
You should just post the output of "\d yourtable" from psql, it would be=
=20=20
quicker than writing a paragraph... Be lazy, lol.
So, basically if I understand you are doing a self-join on your
table,=20=
=20
you want all rows from the same day, and you're doing something with
the=20=
=20
dates, and...
Tip for getting answers from this list :
Explain (in english) what your query actually does, someone might come
up=
=20=20
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=20=20
> 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=20=20
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=20=
=20
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=20=
=20
the time, I presume you only use the time for display purposes) I
would=20=
=20
just store the timestamptz in "calltimestamp" and the date in "calldate",=
=20=20
with a trigger to ensure the date is set to calltimestamp::date every
time=
=20=20
a row is inserted/updated.
This is better than a function index since you use that column a lot
in=20=
=20
your query, it will be slightly faster, and it will save a lot of=20=20
timestamptz->date casts hence it will save CPU cycles
Try this last option (separate date column), and repost EXPLAIN
ANALYZE=20=
=20
of your query so it can be optimized further.
Also, PLEASE don't use substr(), use a.dst LIKE '*100%', look in
the=20=20
manual. LIKE 'foo%' is indexable if you create the proper index.
--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
