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 19:16:29 +0800
Message-ID: <47FCA58D.5070904@postnewspapers.com.au> (raw)
In-Reply-To: <931174.41521.qm@web33505.mail.mud.yahoo.com>
References: <931174.41521.qm@web33505.mail.mud.yahoo.com>
Luigi N. Puleio wrote:
> 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?
Can you post the full EXPLAIN ANALYZE from the query? This snippet
doesn't even show how records are being looked up.
What about a \d of the table from psql, or at least a summary of the
involved column data types and associated indexes?
--
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: <47FCA58D.5070904@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