BTW, will it improve something if you change your index to "my_table(
id, the_date )"?
Rgds,
-Dimitri
On 9/5/07, JS Ubei <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I need to improve a query like :
>
> SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
>
> Stupidly, I create a B-tree
Gregory Stark <[EMAIL PROTECTED]> writes:
> You can simulate such a plan with the subqueries I described but
> there's a bit more overhead than necessary and you need a reasonably
> efficient source of the distinct ids.
Yeah, that seems like the $64 question. If you have no better way of
finding
"Pavel Stehule" <[EMAIL PROTECTED]> writes:
>>
>> why not
>>
>> select id,
>>min(the_date) as min_date,
>>max(the_date) as max_date
>> from my_table group by id;
>>
>> Since 8.0 or was it earlier this will use an index should a reasonable one
>> exist.
As I mentioned in the othe
"Peter Childs" <[EMAIL PROTECTED]> writes:
> My personal reaction is why are you using distinct at all?
>
> why not
>
> select id,
>min(the_date) as min_date,
>max(the_date) as max_date
> from my_table group by id;
>
> Since 8.0 or was it earlier this will use an index should a r
"hubert depesz lubaczewski" <[EMAIL PROTECTED]> writes:
> On Wed, Sep 05, 2007 at 12:30:21PM +0100, Gregory Stark wrote:
>> SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY id,
>> the_date ASC
>> SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY id,
>
On Wed, Sep 05, 2007 at 12:30:21PM +0100, Gregory Stark wrote:
> SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY id,
> the_date ASC
> SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY id,
> the_date DESC
> I think the first of these can actually use y
>
> why not
>
> select id,
>min(the_date) as min_date,
>max(the_date) as max_date
> from my_table group by id;
>
> Since 8.0 or was it earlier this will use an index should a reasonable one
> exist.
without any limits, seq scan is optimal.
Regards
Pavel Stehule
On 05/09/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
> "Gregory Stark" <[EMAIL PROTECTED]> writes:
>
> > "JS Ubei" <[EMAIL PROTECTED]> writes:
> >
> >> I need to improve a query like :
> >>
> >> SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
> >...
> > I don't think you'll fi
"Gregory Stark" <[EMAIL PROTECTED]> writes:
> "JS Ubei" <[EMAIL PROTECTED]> writes:
>
>> I need to improve a query like :
>>
>> SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
>...
> I don't think you'll find anything much faster for this particular query. You
> could profile ru
"JS Ubei" <[EMAIL PROTECTED]> writes:
> Hi all,
>
> I need to improve a query like :
>
> SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
>
> Stupidly, I create a B-tree index on my_table(the_date), witch is logically
> not used in my query, because it's not with a constant ? isn
bad address kep his from going to the list on my first try ... apologies to the
moderators.
-Original Message-
From: Gregory Williamson
Sent: Wed 9/5/2007 4:59 AM
To: JS Ubei; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] optimize query with a maximum(date) extraction
In
Hi all,
I need to improve a query like :
SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
Stupidly, I create a B-tree index on my_table(the_date), witch is logically not
used in my query, because it's not with a constant ? isn't it ?
I know that I can't create a function inde
12 matches
Mail list logo
