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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
