pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed to increase performance of a query
20+ messages / 8 participants
[nested] [flat]
* Need to increase performance of a query
@ 2010-06-10 17:50 Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:33 ` Re: Need to increase performance of a query hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 17:50 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have the following query that needs tuning:
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
psrdb(#
psrdb(# )
psrdb-# ORDER BY
psrdb-# maxRank DESC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=0.19..0.19 rows=1 width=0) (actual time=12.154..12.155
rows=1 loops=1)
Sort Key: ($0)
Sort Method: quicksort Memory: 17kB
InitPlan
-> Limit (cost=0.00..0.17 rows=1 width=8) (actual
time=12.129..12.130 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=12.126..12.126
rows=1 loops=1)
Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND
((project_id)::text = 'proj2783'::text))
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=12.140..12.142 rows=1 loops=1)
Total runtime: 12.206 ms
(9 rows)
I have been playing with indexes but it seems that it doesn't make any
difference. (I have created an index: item_rank_index" btree
(project_id) WHERE (pf_id IS NULL))
Any advice on how to make it run faster?
Thanks a lot,
Anne
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 18:12 ` Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
1 sibling, 1 reply; 20+ messages in thread
From: Jesper Krogh @ 2010-06-10 18:12 UTC (permalink / raw)
To: pgsql-performance
On 2010-06-10 19:50, Anne Rosset wrote:
> Any advice on how to make it run faster?
What timing do you get if you run it with \t (timing on) and without
explain analyze ?
I would be surprised if you can get it much faster than what is is.. I
may be that a
significant portion is "planning cost" so if you run it a lot you might
benefit from
a prepared statement.
--
Jesper
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
@ 2010-06-10 18:36 ` Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
0 siblings, 1 reply; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 18:36 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: pgsql-performance
Jesper Krogh wrote:
> On 2010-06-10 19:50, Anne Rosset wrote:
>> Any advice on how to make it run faster?
>
> What timing do you get if you run it with \t (timing on) and without
> explain analyze ?
>
> I would be surprised if you can get it much faster than what is is.. I
> may be that a
> significant portion is "planning cost" so if you run it a lot you
> might benefit from
> a prepared statement.
>
>
Hi Jesper,
Thanks your response:
psrdb=# \timing
Timing is on.
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;
maxrank
-------------
20200000000
(1 row)
Time: 12.947 ms
It really seems to me that it should take less time.
Specially when I see the result with a different where clause like
this one:
psrdb=# SELECT
psrdb-# MAX(item_rank.rank) AS maxRank
psrdb-# FROM
psrdb-# item_rank item_rank
psrdb-# WHERE
psrdb-# item_rank.pf_id='plan1408'
psrdb-# ORDER BY
psrdb-# maxRank DESC;
maxrank
-------------
20504000000
(1 row)
Time: 2.582 ms
Thanks,
Anne
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 19:22 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
0 siblings, 1 reply; 20+ messages in thread
From: Jochen Erwied @ 2010-06-10 19:22 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: pgsql-performance
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?
--
Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
@ 2010-06-10 19:34 ` Anne Rosset <arosset@collab.net>
2010-06-10 19:37 ` Re: Need to increase performance of a query Kenneth Marshall <ktm@rice.edu>
2010-06-10 19:39 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
0 siblings, 3 replies; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 19:34 UTC (permalink / raw)
To: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 19:37 ` Kenneth Marshall <ktm@rice.edu>
2010-06-10 19:42 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2 siblings, 1 reply; 20+ messages in thread
From: Kenneth Marshall @ 2010-06-10 19:37 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:37 ` Re: Need to increase performance of a query Kenneth Marshall <ktm@rice.edu>
@ 2010-06-10 19:42 ` Anne Rosset <arosset@collab.net>
0 siblings, 0 replies; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 19:42 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 19:39 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:44 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2 siblings, 1 reply; 20+ messages in thread
From: Jochen Erwied @ 2010-06-10 19:39 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: pgsql-performance
Thursday, June 10, 2010, 9:34:07 PM you wrote:
> Time: 1.516 ms
> Time: 13.177 ms
I'd suppose the first query to scan a lot less rows than the second one.
Could you supply an explained plan for the fast query?
--
Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:39 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
@ 2010-06-10 19:44 ` Anne Rosset <arosset@collab.net>
0 siblings, 0 replies; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 19:44 UTC (permalink / raw)
To: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; +Cc: pgsql-performance
Jochen Erwied wrote:
> Thursday, June 10, 2010, 9:34:07 PM you wrote:
>
>
>> Time: 1.516 ms
>>
>
>
>> Time: 13.177 ms
>>
>
> I'd suppose the first query to scan a lot less rows than the second one.
> Could you supply an explained plan for the fast query?
>
>
Hi Jochen,
Here is the explained plan for the fastest query:
psrdb=# explain analyze ELECT
psrdb-# MAX(item_rank.rank) AS maxRank
psrdb-# FROM
psrdb-# item_rank item_rank
psrdb-# WHERE
psrdb-# item_rank.pf_id='plan1408';
ERROR: syntax error at or near "ELECT" at character 17
psrdb=# explain analyze SELECT
psrdb-# MAX(item_rank.rank) AS maxRank
psrdb-# FROM
psrdb-# item_rank item_rank
psrdb-# WHERE
psrdb-# item_rank.pf_id='plan1408';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.28..8.29 rows=1 width=8) (actual time=0.708..0.709
rows=1 loops=1)
-> Index Scan using item_rank_pf on item_rank (cost=0.00..8.27
rows=1 width=8) (actual time=0.052..0.407 rows=303 loops=1)
Index Cond: ((pf_id)::text = 'plan1408'::text)
Total runtime: 0.761 ms
(4 rows)
Time: 2.140 ms
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 19:47 ` Craig James <craig_james@emolecules.com>
2010-06-10 19:56 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:57 ` Re: Need to increase performance of a query Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2 siblings, 2 replies; 20+ messages in thread
From: Craig James @ 2010-06-10 19:47 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
@ 2010-06-10 19:56 ` Anne Rosset <arosset@collab.net>
2010-06-10 20:10 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 19:56 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
2010-06-10 19:56 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 20:10 ` Joe Conway <mail@joeconway.com>
2010-06-10 20:13 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 20+ messages in thread
From: Joe Conway @ 2010-06-10 20:10 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
On 06/10/2010 12:56 PM, Anne Rosset wrote:
> Craig James wrote:
>> 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)
> 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
try:
create index item_rank_null_idx on item_rank(pf_id)
where rank IS NOT NULL AND pf_id IS NULL;
Joe
Attachments:
signature.asc (application/pgp-signature, 898B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
2010-06-10 19:56 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 20:10 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
@ 2010-06-10 20:13 ` Joe Conway <mail@joeconway.com>
2010-06-10 20:21 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
0 siblings, 1 reply; 20+ messages in thread
From: Joe Conway @ 2010-06-10 20:13 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
On 06/10/2010 01:10 PM, Joe Conway wrote:
> try:
>
> create index item_rank_null_idx on item_rank(pf_id)
> where rank IS NOT NULL AND pf_id IS NULL;
oops -- that probably should be:
create index item_rank_null_idx on item_rank(project_id)
where rank IS NOT NULL AND pf_id IS NULL;
Joe
Attachments:
signature.asc (application/pgp-signature, 898B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
2010-06-10 19:56 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 20:10 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
2010-06-10 20:13 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
@ 2010-06-10 20:21 ` Anne Rosset <arosset@collab.net>
2010-06-10 21:06 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 20:21 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
Joe Conway wrote:
> On 06/10/2010 01:10 PM, Joe Conway wrote:
>
>> try:
>>
>> create index item_rank_null_idx on item_rank(pf_id)
>> where rank IS NOT NULL AND pf_id IS NULL;
>>
>
> oops -- that probably should be:
>
> create index item_rank_null_idx on item_rank(project_id)
> where rank IS NOT NULL AND pf_id IS NULL;
>
> Joe
>
>
I tried that and it didn't make any difference. Same query plan.
Anne
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
2010-06-10 19:56 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 20:10 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
2010-06-10 20:13 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
2010-06-10 20:21 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 21:06 ` Joe Conway <mail@joeconway.com>
2010-06-10 21:20 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
0 siblings, 1 reply; 20+ messages in thread
From: Joe Conway @ 2010-06-10 21:06 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
On 06/10/2010 01:21 PM, Anne Rosset wrote:
>>
> I tried that and it didn't make any difference. Same query plan.
A little experimentation suggests this might work:
create index item_rank_project on item_rank(project_id, rank) where
pf_id IS NULL;
Joe
Attachments:
signature.asc (application/pgp-signature, 898B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
2010-06-10 19:56 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 20:10 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
2010-06-10 20:13 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
2010-06-10 20:21 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 21:06 ` Re: Need to increase performance of a query Joe Conway <mail@joeconway.com>
@ 2010-06-10 21:20 ` Anne Rosset <arosset@collab.net>
0 siblings, 0 replies; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 21:20 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
Joe Conway wrote:
> On 06/10/2010 01:21 PM, Anne Rosset wrote:
>
>>>
>>>
>> I tried that and it didn't make any difference. Same query plan.
>>
>
> A little experimentation suggests this might work:
>
> create index item_rank_project on item_rank(project_id, rank) where
> pf_id IS NULL;
>
> Joe
>
>
Yes it does. Thanks a lot!
Anne
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
@ 2010-06-10 19:57 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-10 20:08 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
1 sibling, 1 reply; 20+ messages in thread
From: Heikki Linnakangas @ 2010-06-10 19:57 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Anne Rosset <arosset@collab.net>; pgsql-performance
On 10/06/10 22:47, Craig James wrote:
> 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.
That was addressed in version 8.3. 8.3 and upwards can use an index for
IS NULL.
I believe the NULLs were stored in the index in earlier releases too,
they just couldn't be searched for.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
2010-06-10 19:57 ` Re: Need to increase performance of a query Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
@ 2010-06-10 20:08 ` Anne Rosset <arosset@collab.net>
2010-06-10 20:59 ` Re: Need to increase performance of a query Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
0 siblings, 1 reply; 20+ messages in thread
From: Anne Rosset @ 2010-06-10 20:08 UTC (permalink / raw)
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
Heikki Linnakangas wrote:
> On 10/06/10 22:47, Craig James wrote:
>> 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.
>
> That was addressed in version 8.3. 8.3 and upwards can use an index
> for IS NULL.
>
> I believe the NULLs were stored in the index in earlier releases too,
> they just couldn't be searched for.
>
I am using postgres 8.3.6. So why doesn't it use my index?
Thanks,
Anne
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Re: Need to increase performance of a query Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Re: Need to increase performance of a query Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Re: Need to increase performance of a query Craig James <craig_james@emolecules.com>
2010-06-10 19:57 ` Re: Need to increase performance of a query Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-10 20:08 ` Re: Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 20:59 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
0 siblings, 0 replies; 20+ messages in thread
From: Heikki Linnakangas @ 2010-06-10 20:59 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
On 10/06/10 23:08, Anne Rosset wrote:
> Heikki Linnakangas wrote:
>> On 10/06/10 22:47, Craig James wrote:
>>> 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.
>>
>> That was addressed in version 8.3. 8.3 and upwards can use an index
>> for IS NULL.
>>
>> I believe the NULLs were stored in the index in earlier releases too,
>> they just couldn't be searched for.
>>
> I am using postgres 8.3.6. So why doesn't it use my index?
Well, apparently the planner doesn't think it would be any cheaper.
I wonder if this helps:
CREATE INDEX item_rank_project_id ON item_rank(project_id, rank, pf_id);
And make sure you drop any of the indexes that are not being used, to
make sure the planner doesn't choose them instead.
(You should upgrade to 8.3.11, BTW. There's been a bunch of bug-fixes
in-between, though I don't know if any are related to this, but there's
other important fixes there)
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Need to increase performance of a query
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
@ 2010-06-10 18:33 ` hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 20+ messages in thread
From: hubert depesz lubaczewski @ 2010-06-10 18:33 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: pgsql-performance
On Thu, Jun 10, 2010 at 10:50:40AM -0700, Anne Rosset wrote:
> Any advice on how to make it run faster?
First, let me ask a simple question - what runtime for this query will
be satisfactory for you?
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2010-06-10 21:20 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-10 17:50 Need to increase performance of a query Anne Rosset <arosset@collab.net>
2010-06-10 18:12 ` Jesper Krogh <jesper@krogh.cc>
2010-06-10 18:36 ` Anne Rosset <arosset@collab.net>
2010-06-10 19:22 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:34 ` Anne Rosset <arosset@collab.net>
2010-06-10 19:37 ` Kenneth Marshall <ktm@rice.edu>
2010-06-10 19:42 ` Anne Rosset <arosset@collab.net>
2010-06-10 19:39 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-06-10 19:44 ` Anne Rosset <arosset@collab.net>
2010-06-10 19:47 ` Craig James <craig_james@emolecules.com>
2010-06-10 19:56 ` Anne Rosset <arosset@collab.net>
2010-06-10 20:10 ` Joe Conway <mail@joeconway.com>
2010-06-10 20:13 ` Joe Conway <mail@joeconway.com>
2010-06-10 20:21 ` Anne Rosset <arosset@collab.net>
2010-06-10 21:06 ` Joe Conway <mail@joeconway.com>
2010-06-10 21:20 ` Anne Rosset <arosset@collab.net>
2010-06-10 19:57 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-10 20:08 ` Anne Rosset <arosset@collab.net>
2010-06-10 20:59 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-06-10 18:33 ` hubert depesz lubaczewski <depesz@depesz.com>
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