DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=rocketmail.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=cNELoD1xgdB7aASuEx4+gPq9MN793kOvPcShbV1RDglPduNSE3GM/w+w2msOIUbR2BM7iWSFm1qTQhOtUpXss4iW0bM7D/Ri/FlbAkT13mKhyge0W96bGT3YBFuj5PTD4ab70p8aVUfMPKc/8L9z12FrOMQVKhoeHFFugpOQ4HU=;
Date: Wed, 9 Apr 2008 06:45:59 -0700 (PDT)
From: "Luigi N. Puleio" <npuleio@rocketmail.com>
Subject: Re: EXPLAIN detail
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Message-ID: <426781.737.qm@web33504.mail.mud.yahoo.com>

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