Re: EXPLAIN detail - Mailing list pgsql-performance

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