From: Anne Rosset <arosset@collab.net>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need to increase performance of a query
Date: Thu, 10 Jun 2010 12:56:49 -0700
Message-ID: <4C114381.3030900@collab.net> (raw)
In-Reply-To: <4C11413B.8080107@emolecules.com>
References: <4C1125F0.8090101@collab.net>
<4C112B19.6080908@krogh.cc>
<4C113098.5040105@collab.net>
<98414706.20100610212211@erwied.eu>
<4C113E2F.6000305@collab.net>
<4C11413B.8080107@emolecules.com>
Craig James wrote:
> On 6/10/10 12:34 PM, 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?
>
> Postgres normally doesn't index NULL values even if the column is
> indexed, so it has to do a table scan when your query includes an IS
> NULL condition. You need to create an index that includes the "IS
> NULL" condition.
>
> create index item_rank_null_idx on item_rank(pf_id)
> where item_rank.pf_id is null;
>
> Craig
>
Hi Craig,
I tried again after adding your suggested index but I didn't see any
improvements: (seems that the index is not used)
psrdb=# explain analyze 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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.17..0.18 rows=1 width=0) (actual time=11.942..11.943
rows=1 loops=1)
InitPlan
-> Limit (cost=0.00..0.17 rows=1 width=8) (actual
time=11.931..11.932 rows=1 loops=1)
-> Index Scan Backward using item_rank_rank on item_rank
(cost=0.00..2933.84 rows=17558 width=8) (actual time=11.926..11.926
rows=1 loops=1)
Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND
((project_id)::text = 'proj2783'::text))
Total runtime: 11.988 ms
(6 rows)
Time: 13.654 ms
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, craig_james@emolecules.com
Subject: Re: Need to increase performance of a query
In-Reply-To: <4C114381.3030900@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