From: PFC <lists@peufeu.com>
To: Luigi N. Puleio <npuleio@rocketmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: EXPLAIN detail
Date: Wed, 09 Apr 2008 20:41:29 +0200
Message-ID: <op.t9cwvfgecigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <981759.47577.qm@web33504.mail.mud.yahoo.com>
References: <981759.47577.qm@web33504.mail.mud.yahoo.com>
> 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 the
> 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 be
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 = (a.calldate)::date AND e.src = a.src
> AND e.lastdata = '/dati/ita/logoutok' AND e.calldate >=
> a.calldate)
> WHERE
> (a.calldate)::date = '2008-04-09'
> AND a.src = '410'
> AND substr(a.dst, 1, 4) = '*100'
> AND a.lastdata = '/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 = '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 >= '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 time
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, npuleio@rocketmail.com, craig@postnewspapers.com.au
Subject: Re: EXPLAIN detail
In-Reply-To: <op.t9cwvfgecigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox