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 i
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
signature.asc
Description: OpenPGP digi
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 a
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
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
signatu
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 we
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
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
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(# )
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 no
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: joc...@erwied.eu +49-208-38800-18, FAX: -19
Saue
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(# WHER
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(#
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 NUL
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 fo
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(#
p
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 porti
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.depes
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"
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(#
20 matches
Mail list logo
