From: Craig Ringer <craig@postnewspapers.com.au>
To: Luigi N. Puleio <npuleio@rocketmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: EXPLAIN detail
Date: Wed, 09 Apr 2008 22:15:06 +0800
Message-ID: <47FCCF6A.8080405@postnewspapers.com.au> (raw)
In-Reply-To: <426781.737.qm@web33504.mail.mud.yahoo.com>
References: <426781.737.qm@web33504.mail.mud.yahoo.com>
Luigi N. Puleio wrote:
>> 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.
--
Craig Ringer
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: craig@postnewspapers.com.au, npuleio@rocketmail.com
Subject: Re: EXPLAIN detail
In-Reply-To: <47FCCF6A.8080405@postnewspapers.com.au>
* 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