>> With all that casting, is it possible that appropriate indexes aren't
>> being used because your WHERE / ON clauses aren't an exact type match
>> for the index?
>
> You mean to put an index on date with timestamptz datatype column?...

> Er ... I'm not quite sure what you mean. Do you mean an index on a cast
> of the column, eg:

> CREATE INDEX some_idx_name ON some_table ( some_timestamp_field::date )

> then ... maybe. It's hard to be sure when there is so little information
> available. It shouldn't be necessary, but there are certainly uses for
> that sort of thing - for example, I use a couple of functional indexes
> in the schema I'm working on at the moment. It's probably a good idea to
> look at ways to avoid doing that first, though.

>> Can you post the full EXPLAIN ANALYZE from the query? This snippet
>> doesn't even show how records are being looked up.
>
> HashAggregate (cost=52236.31..52236.33 rows=1 width=34) (actual time=7004.779...7004.782 rows=1 loops=1)
> -> Nested Loop (cost=0.00..52236.30 rows=1 width=34) (actual time=3939.450..7004.592 rows=1 loops=1)
> Join filter: (("inner".calldate)::time without time zone => ("outer".calldate)::time without time zone)
> -> Seq Scan on table a (cost=0.00..27444.03 rows=1 width=26) (actual time=2479.199..2485.266 rows=3 loops=1)
> Filter: (((calldate)::date = '2008-04-09'::date) AND ((src)::text = '410'::text) AND (substr((dst)::text, 1, 4)='*100'::text) AND ((lastdata)::text ='/dati/ita/loginok'::text))
> ->Seq Scan on table b (cost=0.00..24792.22 rows=3 width=16) (actual time=1504.508..1506.374 rows=1 loops=3)
> Filter: ((((lastdata)::text ='/dati/ita/logoutok'::text) AND ('410'::text=(src)::text) AND ('2008-04-09'::date = (calldate)::date))
> Total runtime: 7005.706 ms

> Personally, I'd want to get rid of all those casts first. Once that's
> cleaned up I'd want to look at creating appropriate indexes on your
> tables. If necessary, I might even create a composite index on
> (lastdata,src,calldate) .

>> What about a \d of the table from psql, or at least a summary of the
>> involved column data types and associated indexes?
>
> this table has an acctid column which is PK then most of the other columns are varchar(80) or so....

> Do you mean that the columns involved in your WHERE and ON clauses, the
> ones you're casting to date, timestamp, etc, are stored as VARCHAR? If
> so, it's no surprise that the query is slow because you're forcing
> PostgreSQL to convert a string to a date, timestamp, or time datatype to
> do anything with it ... and you're doing it many times in every query.
> That will be VERY slow, and prevent the use of (simple) indexes on those
> columns.

> If you're really storing dates/times as VARCHAR, you should probably
> look at some changes to your database design, starting with the use of
> appropriate data types.

> That's all guesswork, because you have not provided enough information.

> Can you please post the output of psql's \d command on the table in
> question?

> If for some reason you cannot do that, please at least include the data
> type of the primary key and all fields involved in the query, as well as
> a list of all the indexes on both tables.

> The easy way to do that is to just launch "psql" then run:

> \d table

> and paste the output to an email.

> So for 4 records result, 7 seconds are too way a lot I guess... but as I said before I'm gonna wait if > the responsible of the server did a VACUUM on the table...
>
> What do you think?...

> If you're really casting VARCHAR to DATE, TIME, TIMESTAMP, etc on demand
> then personally I really doubt that dead rows are your problem.


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:

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





__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Luigi N. Puleio wrote:
>> If for some reason you cannot do that, please at least include the data
>> type of the primary key and all fields involved in the query, as well as
>> a list of all the indexes on both tables.
If you won't show people on the list your table definitions, or at least
the information shown above, then it's less likely that anybody can help
you or will spend the time trying to help you.

Personally I think you may need some functional/cast, and possibly
composite, indexes to avoid the looping sequential scan as I said
before. However, that's guesswork without some more information as
repeatedly stated and requested. I'm not going to bother replying to any
further mail just to say so again.


Try reading the documentation chapter about indexes:

http://www.postgresql.org/docs/curre...c/indexes.html

and about query optimisation:

http://www.postgresql.org/docs/curre...ance-tips.html

then experiment with various indexes to see what works best. Think about
the data types. Remember that you can build an index on a cast of a
field, on multiple fields, on function calls, or basically any other
simple expression or expressions, but that complex indexes will cost
more to build and maintain and might be bigger (and thus slower to search).


Anyway, I'm done.

--
Craig Ringer

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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':ATE + '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.








--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Wed, Apr 9, 2008 at 11:41 AM, PFC <lists@peufeu.com> wrote:
> In order to use the index, you could rewrite it as something like :
> a.calldate >= '2008-04-09' AND a.calldate < ('2008-04-09':ATE + '1
> DAY'::INTERVAL)
> This is a RANGE query (just like BETWEEN) which is index-friendly.

Another option would be to create a functional index on date_trunc(
'day', cdr.calldate)

then using a where condition like:

date_trunc(a.calldate) = '2008-04-09'

would definitely use an index.


--
Regards,
Richard Broersma Jr.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance