From: Luigi N. Puleio <npuleio@rocketmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: EXPLAIN detail
Date: Wed, 9 Apr 2008 06:45:59 -0700 (PDT)
Message-ID: <426781.737.qm@web33504.mail.mud.yahoo.com> (raw)
>> SELECT
>> (a.column1)::date, MIN(b.column2) - a.column2
>> FROM
>> table a
>> inner join table b
>> on ((a.column1)::date = (b.column1)::date amd
>> b.column3 = 'b' and (b.column1)::time without time
>> zone >= (a.column1)::time without time zone)
>> WHERE
>> (a.column1)::date = '2008-04-09'
>> a.column3 = 'a'
>> GROUP BY a.column1
>>
>> and with this I have to obtain like 3-4 records from
>> all those whole 500000 records and with the explain
>> analyze I get almost 6 seconds:
>>
>> Nested Loop (cost=0.00...52140.83 rows=1 width=34)
>> (actual time=4311.756...5951.271 rows=1 loops=1)
> 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?...
> 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
> 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....
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?...
Thanks again to all.
Ciao,
Luigi
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
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: npuleio@rocketmail.com
Subject: Re: EXPLAIN detail
In-Reply-To: <426781.737.qm@web33504.mail.mud.yahoo.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