Re: EXPLAIN detail - Mailing list pgsql-performance

> 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.