Re: Specific query performance problem help requested - postgresql 7.4

Looks like I modified that constraint since the original has '3' and
explaining that shows the one I ended up running and posting has 3.
Whn I explain on the original version it shows filter: (bucket =
3::bigint)

Can you elaborate on what you mean by:
> The bucket = 3 rows have to be lurking further to the back of the
filename order than the others

How does this apply to the index on filename?

It is possible that the data values are skewed, is there any way I can
gracefully handle this condition?
This query is being used to extract data for interactive display and the
time for bucket 3 is so out of
whack with all the others (I've run this across all buckets and only
bucket 3 has the horrendous excecution
times)

Any suggestions for working around this problem to speed up execution?

Thanks for the help

brad

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, May 26, 2005 1:32 PM
To: Brad Might
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Specific query performance problem help requested
- postgresql 7.4

"Brad Might" <bmight(at)storediq(dot)com> writes:
> Can someone help me break this down and figure out why the one query
> takes so much longer than the other?

It looks to me like there's a correlation between filename and bucket,
such that the indexscan in filename order takes much longer to run
across the first 25 rows with bucket = 3 than it does to run across the
first 25 with bucket = 7 or bucket = 8. It's not just a matter of there
being fewer rows with bucket = 3 ... the cost differential is much
larger than is explained by the count ratios. The bucket = 3 rows have
to be lurking further to the back of the filename order than the others.

> Here's the bucket distribution..i have clustered the index on the
> bucket value.

If you have an index on bucket, it's not doing you any good here anyway,
since you wrote the constraint as a crosstype operator ("3" is int4 not
int8). It might help to explicitly cast the constant to int8.

regards, tom lane