From: Anne Rosset <arosset@collab.net>
To: Kenneth Marshall <ktm@rice.edu>
Cc: Jochen Erwied <jochen@pgsql-performance.erwied.eu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need to increase performance of a query
Date: Thu, 10 Jun 2010 12:42:21 -0700
Message-ID: <4C11401D.5000400@collab.net> (raw)
In-Reply-To: <20100610193712.GL3063@aart.is.rice.edu>
References: <4C1125F0.8090101@collab.net>
<4C112B19.6080908@krogh.cc>
<4C113098.5040105@collab.net>
<98414706.20100610212211@erwied.eu>
<4C113E2F.6000305@collab.net>
<20100610193712.GL3063@aart.is.rice.edu>
Kenneth Marshall wrote:
> On Thu, Jun 10, 2010 at 12:34:07PM -0700, Anne Rosset wrote:
>
>> Jochen Erwied wrote:
>>
>>> Thursday, June 10, 2010, 8:36:08 PM you wrote:
>>>
>>>
>>>
>>>> psrdb=# (SELECT
>>>> psrdb(# MAX(item_rank.rank) AS maxRank
>>>> psrdb(# FROM
>>>> psrdb(# item_rank item_rank
>>>> psrdb(# WHERE
>>>> psrdb(# item_rank.project_id='proj2783'
>>>> psrdb(# AND item_rank.pf_id IS NULL
>>>> psrdb(#
>>>> psrdb(# )
>>>> psrdb-# ORDER BY
>>>> psrdb-# maxRank DESC;
>>>>
>>>>
>>> Don't think it does really matter, but why do you sort a resultset
>>> consisting of only one row?
>>>
>>>
>>>
>> Sorry, I should have removed the ORDER by (the full query has a union).
>> So without the ORDER by, here are the results:
>> psrdb=# SELECT
>> psrdb-# MAX(item_rank.rank) AS maxRank
>> psrdb-# FROM
>> psrdb-# item_rank item_rank
>> psrdb-# WHERE
>> psrdb-# item_rank.pf_id='plan1408';
>> maxrank
>> -------------
>> 20504000000
>> (1 row)
>>
>> Time: 1.516 ms
>> psrdb=# SELECT
>> psrdb-# MAX(item_rank.rank) AS maxRank
>> psrdb-# FROM
>> psrdb-# item_rank item_rank
>> psrdb-# WHERE
>> psrdb-# item_rank.project_id='proj2783'
>> psrdb-# AND item_rank.pf_id IS NULL;
>> maxrank
>> -------------
>> 20200000000
>> (1 row)
>>
>> Time: 13.177 ms
>>
>> Is there anything that can be done for the second one?
>>
>> Thanks,
>> Anne
>>
>>
> What about an IS NULL index on pf_id?
>
> Regards,
> Ken
>
Hi Ken,
I have the following index:
"item_rank_index2" btree (project_id) WHERE (pf_id IS NULL)
Are you suggesting something else?
Thanks,
Anne
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: arosset@collab.net, ktm@rice.edu, jochen@pgsql-performance.erwied.eu
Subject: Re: Need to increase performance of a query
In-Reply-To: <4C11401D.5000400@collab.net>
* 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