pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex oddity
12+ messages / 6 participants
[nested] [flat]
* Index oddity
@ 2004-06-09 19:31 ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 12+ messages in thread
From: ken @ 2004-06-09 19:31 UTC (permalink / raw)
To: pgsql-performance
I'm having a performance issue that I just can't resolve and its very,
very curious. Thought someone here might be able to shed some light on
the subject.
I'm using Postgres 7.4.2 on Red Hat 9. I have a table with 763,809 rows
in it defined as follows ...
ksedb=# \d nrgfeature
Table "public.nrgfeature"
Column | Type | Modifiers
----------------+-----------------------------+-----------
fid1 | numeric(64,0) | not null
fid2 | numeric(64,0) | not null
created | timestamp without time zone | not null
createdby | character varying(30) | not null
modified | timestamp without time zone |
modifiedby | character varying(30) |
geommodified | timestamp without time zone |
geommodifiedby | character varying(30) |
deleted | timestamp without time zone |
deletedby | character varying(30) |
featuretypeid | smallint | not null
description | text |
datasourceid | smallint | not null
lowerleftx | double precision | not null
lowerlefty | double precision | not null
upperrightx | double precision | not null
upperrighty | double precision | not null
diagonalsize | double precision |
login | character varying(25) |
Indexes:
"nrgfeature_pkey" primary key, btree (fid1, fid2)
"nrgfeature_ft_index" btree (featuretypeid)
"nrgfeature_xys_index" btree (upperrightx, lowerleftx, upperrighty,
lowerlefty, diagonalsize)
Inherits: commonfidattrs,
commonrevisionattrs
... If I write a query as follows ...
SELECT *
FROM nrgfeature f
WHERE
upperRightX > 321264.23697721504
AND lowerLeftX < 324046.79981208267
AND upperRightY > 123286.26189863647
AND lowerLeftY < 124985.92745047594
AND diagonalSize > 50.000
;
... (or any value for diagonalsize over 50) then my query runs in 50-100
milliseconds. However, if the diagonalSize value is changed to 49.999
or any value below 50, then the query takes over a second for a factor
of 10 degradation in speed, even though the exact same number of rows is
returned.
The query plan for diagonalSize > 50.000 is ...
Index Scan using nrgfeature_xys_index on nrgfeature f
(cost=0.00..17395.79 rows=4618 width=220)
Index Cond: ((upperrightx > 321264.236977215::double precision) AND
(lowerleftx < 324046.799812083::double precision) AND (upperrighty >
123286.261898636::double precision) AND (lowerlefty <
124985.927450476::double precision) AND (diagonalsize > 50::double
precision))
... while for diagonalSize > 49.999 is ...
Seq Scan on nrgfeature f (cost=0.00..31954.70 rows=18732 width=220)
Filter: ((upperrightx > 321264.236977215::double precision) AND
(lowerleftx < 324046.799812083::double precision) AND (upperrighty >
123286.261898636::double precision) AND (lowerlefty <
124985.927450476::double precision) AND (diagonalsize > 49.999::double
precision))
... and yes, if I set enable_seqscan=false then the index is forced to
be used. However, despite this being an undesirable solution for this
simple case it doesn't solve the problem for the general case. As soon
as I add in joins with a couple of tables to perform the actual query I
want to perform, the seq scan setting doesn't force the index to be used
anymore. Instead, the primary key index is used at this same
diagonalSize cutoff and the 5-part double precision clause is used as a
filter to the index scan and the result is again a very slow query.
I can provide those queries and results but that would only complicate
this already lengthy email and the above seems to be the crux of the
problem anyway.
Any help or thoughts would be greatly appreciated of course.
Thanks,
Ken Southerland
--
------s----a----m----s----i----x----e----d----d------
--
Ken Southerland
Senior Consultant
Sam Six EDD
http://www.samsixedd.com
503-236-4288 (office)
503-358-6542 (cell)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
@ 2004-06-09 20:12 ` Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
0 siblings, 1 reply; 12+ messages in thread
From: Rod Taylor @ 2004-06-09 20:12 UTC (permalink / raw)
To: ken <southerland@samsixedd.com>; +Cc: pgsql-performance
It seems to believe that the number of rows returned for the >49.999
case will be 4 times the number for the >50 case. If that was true, then
the sequential scan would be correct.
ALTER TABLE <table> ALTER COLUMN diagonalsize SET STATISTICS 1000;
ANALZYE <table>;
Send back EXPLAIN ANALYZE output for the >49.999 case.
> The query plan for diagonalSize > 50.000 is ...
>
> Index Scan using nrgfeature_xys_index on nrgfeature f
> (cost=0.00..17395.79 rows=4618 width=220)
> Index Cond: ((upperrightx > 321264.236977215::double precision) AND
> (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> 123286.261898636::double precision) AND (lowerlefty <
> 124985.927450476::double precision) AND (diagonalsize > 50::double
> precision))
>
> ... while for diagonalSize > 49.999 is ...
>
> Seq Scan on nrgfeature f (cost=0.00..31954.70 rows=18732 width=220)
> Filter: ((upperrightx > 321264.236977215::double precision) AND
> (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> 123286.261898636::double precision) AND (lowerlefty <
> 124985.927450476::double precision) AND (diagonalsize > 49.999::double
> precision))
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
@ 2004-06-09 20:50 ` ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 12+ messages in thread
From: ken @ 2004-06-09 20:50 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: pgsql-performance
Thanks Rod,
This setting has no effect however. If I set statistics to 1000, or
even 0, (and then reanalyze the table) I see no change in the behaviour
of the query plans. i.e. there is still the odd transtion in the plans
at diagonalSize = 50.
Ken
On Wed, 2004-06-09 at 13:12, Rod Taylor wrote:
> It seems to believe that the number of rows returned for the >49.999
> case will be 4 times the number for the >50 case. If that was true, then
> the sequential scan would be correct.
>
> ALTER TABLE <table> ALTER COLUMN diagonalsize SET STATISTICS 1000;
> ANALZYE <table>;
>
> Send back EXPLAIN ANALYZE output for the >49.999 case.
>
> > The query plan for diagonalSize > 50.000 is ...
> >
> > Index Scan using nrgfeature_xys_index on nrgfeature f
> > (cost=0.00..17395.79 rows=4618 width=220)
> > Index Cond: ((upperrightx > 321264.236977215::double precision) AND
> > (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> > 123286.261898636::double precision) AND (lowerlefty <
> > 124985.927450476::double precision) AND (diagonalsize > 50::double
> > precision))
> >
> > ... while for diagonalSize > 49.999 is ...
> >
> > Seq Scan on nrgfeature f (cost=0.00..31954.70 rows=18732 width=220)
> > Filter: ((upperrightx > 321264.236977215::double precision) AND
> > (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> > 123286.261898636::double precision) AND (lowerlefty <
> > 124985.927450476::double precision) AND (diagonalsize > 49.999::double
> > precision))
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
@ 2004-06-09 20:56 ` Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
0 siblings, 1 reply; 12+ messages in thread
From: Rod Taylor @ 2004-06-09 20:56 UTC (permalink / raw)
To: ken <southerland@samsixedd.com>; +Cc: pgsql-performance
On Wed, 2004-06-09 at 16:50, ken wrote:
> Thanks Rod,
>
> This setting has no effect however. If I set statistics to 1000, or
Okay.. but you never did send EXPLAIN ANALYZE output. I want to know
what it is really finding.
> On Wed, 2004-06-09 at 13:12, Rod Taylor wrote:
> > It seems to believe that the number of rows returned for the >49.999
> > case will be 4 times the number for the >50 case. If that was true, then
> > the sequential scan would be correct.
> >
> > ALTER TABLE <table> ALTER COLUMN diagonalsize SET STATISTICS 1000;
> > ANALZYE <table>;
> >
> > Send back EXPLAIN ANALYZE output for the >49.999 case.
> >
> > > The query plan for diagonalSize > 50.000 is ...
> > >
> > > Index Scan using nrgfeature_xys_index on nrgfeature f
> > > (cost=0.00..17395.79 rows=4618 width=220)
> > > Index Cond: ((upperrightx > 321264.236977215::double precision) AND
> > > (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> > > 123286.261898636::double precision) AND (lowerlefty <
> > > 124985.927450476::double precision) AND (diagonalsize > 50::double
> > > precision))
> > >
> > > ... while for diagonalSize > 49.999 is ...
> > >
> > > Seq Scan on nrgfeature f (cost=0.00..31954.70 rows=18732 width=220)
> > > Filter: ((upperrightx > 321264.236977215::double precision) AND
> > > (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> > > 123286.261898636::double precision) AND (lowerlefty <
> > > 124985.927450476::double precision) AND (diagonalsize > 49.999::double
> > > precision))
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
@ 2004-06-09 21:02 ` ken <southerland@samsixedd.com>
2004-06-09 21:29 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-10 05:01 ` Re: Index oddity Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: ken @ 2004-06-09 21:02 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: pgsql-performance
On Wed, 2004-06-09 at 13:56, Rod Taylor wrote:
> On Wed, 2004-06-09 at 16:50, ken wrote:
> > Thanks Rod,
> >
> > This setting has no effect however. If I set statistics to 1000, or
>
> Okay.. but you never did send EXPLAIN ANALYZE output. I want to know
> what it is really finding.
Ah, sorry, missed the ANALYZE portion of your request (thought you only
wanted the result of explain if it changed due to the setting).
Here is the query plan with statistics on diagonalsize set to the
default (-1) ...
Seq Scan on nrgfeature f (cost=0.00..32176.98 rows=19134 width=218)
(actual time=61.640..1009.414 rows=225 loops=1)
Filter: ((upperrightx > 321264.236977215::double precision) AND
(lowerleftx < 324046.799812083::double precision) AND (upperrighty >
123286.261898636::double precision) AND (lowerlefty <
124985.927450476::double precision) AND (diagonalsize > 49.999::double
precision))
... and here is the plan with statistics set to 1000 ...
Seq Scan on nrgfeature f (cost=0.00..31675.57 rows=18608 width=218)
(actual time=63.544..1002.701 rows=225 loops=1)
Filter: ((upperrightx > 321264.236977215::double precision) AND
(lowerleftx < 324046.799812083::double precision) AND (upperrighty >
123286.261898636::double precision) AND (lowerlefty <
124985.927450476::double precision) AND (diagonalsize > 49.999::double
precision))
... so yeah, its obviously finding way, way less rows than it thinks it
will.
thanks,
ken
>
> > On Wed, 2004-06-09 at 13:12, Rod Taylor wrote:
> > > It seems to believe that the number of rows returned for the >49.999
> > > case will be 4 times the number for the >50 case. If that was true, then
> > > the sequential scan would be correct.
> > >
> > > ALTER TABLE <table> ALTER COLUMN diagonalsize SET STATISTICS 1000;
> > > ANALZYE <table>;
> > >
> > > Send back EXPLAIN ANALYZE output for the >49.999 case.
> > >
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
@ 2004-06-09 21:29 ` Rod Taylor <pg@rbt.ca>
2004-06-09 23:11 ` Re: Index oddity ken <southerland@samsixedd.com>
1 sibling, 1 reply; 12+ messages in thread
From: Rod Taylor @ 2004-06-09 21:29 UTC (permalink / raw)
To: ken <southerland@samsixedd.com>; +Cc: pgsql-performance
> ... and here is the plan with statistics set to 1000 ...
>
> Seq Scan on nrgfeature f (cost=0.00..31675.57 rows=18608 width=218)
> (actual time=63.544..1002.701 rows=225 loops=1)
> Filter: ((upperrightx > 321264.236977215::double precision) AND
> (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> 123286.261898636::double precision) AND (lowerlefty <
> 124985.927450476::double precision) AND (diagonalsize > 49.999::double
> precision))
It's better like this, but still way off the mark. Even your good query
which uses the index was out by more than an order of magnitude.
Try raising the statistics levels for upperrightx, lowerleftx,
upperrighty and lowerlefty.
Failing that, you might be able to push it back down again by giving
diagonalsize an upper limit. Perhaps 500 is a value that would never
occur.
AND (diagonalsize BETWEEN 49.999::double precision AND 500)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 21:29 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
@ 2004-06-09 23:11 ` ken <southerland@samsixedd.com>
2004-06-10 01:45 ` Re: Index oddity Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 12+ messages in thread
From: ken @ 2004-06-09 23:11 UTC (permalink / raw)
To: pgsql-performance
I had already tried setting the statistics to 1000 for all five of these
double precision fields with effectively no improvement. Should have
mentioned that.
Also the between makes all values for diagonalSize bad since it is
effectively doing digonalSize > X and diagonalSize < Y. If I do a query
with the same values for the four x,y values and diagonalSize < Y, then
for Y=49.999 the query is fast but for anything 50.000 and greater the
query is slow. The exact opposite of the greater than queries not
surprisingly.
I also think I originally reported that the two queries gave the same
number of rows. That is not true. It was true when I had other joins
in, but when I stripped the query down to this core problem I should
have noticed that the number of results now differs between the two,
which I didn't at first.
If I take away the diagonalSize condition in my query I find that there
are 225 rows that satisfy the other conditions. 155 of these have a
diagonalSize value of exactly 50.000, while the remaining 70 rows all
have values larger than 50. Thus there is a big discrete jump in the
number of rows at a diagonalSize of 50. However, the statistics are off
by two orders of magnitude in guessing how many rows there are going to
be in this case and thus is not using my index. How can I fix that?
Ken
On Wed, 2004-06-09 at 14:29, Rod Taylor wrote:
> > ... and here is the plan with statistics set to 1000 ...
> >
> > Seq Scan on nrgfeature f (cost=0.00..31675.57 rows=18608 width=218)
> > (actual time=63.544..1002.701 rows=225 loops=1)
> > Filter: ((upperrightx > 321264.236977215::double precision) AND
> > (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> > 123286.261898636::double precision) AND (lowerlefty <
> > 124985.927450476::double precision) AND (diagonalsize > 49.999::double
> > precision))
>
> It's better like this, but still way off the mark. Even your good query
> which uses the index was out by more than an order of magnitude.
>
> Try raising the statistics levels for upperrightx, lowerleftx,
> upperrighty and lowerlefty.
>
> Failing that, you might be able to push it back down again by giving
> diagonalsize an upper limit. Perhaps 500 is a value that would never
> occur.
>
> AND (diagonalsize BETWEEN 49.999::double precision AND 500)
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 21:29 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 23:11 ` Re: Index oddity ken <southerland@samsixedd.com>
@ 2004-06-10 01:45 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-06-10 02:17 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 12+ messages in thread
From: Christopher Kings-Lynne @ 2004-06-10 01:45 UTC (permalink / raw)
To: ken <southerland@samsixedd.com>; +Cc: pgsql-performance
> If I take away the diagonalSize condition in my query I find that there
> are 225 rows that satisfy the other conditions. 155 of these have a
> diagonalSize value of exactly 50.000, while the remaining 70 rows all
> have values larger than 50. Thus there is a big discrete jump in the
> number of rows at a diagonalSize of 50. However, the statistics are off
> by two orders of magnitude in guessing how many rows there are going to
> be in this case and thus is not using my index. How can I fix that?
Maybe you should drop your random_page_cost to something less than 4,
eg. 3 or even 2...
Chris
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 21:29 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 23:11 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-10 01:45 ` Re: Index oddity Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2004-06-10 02:17 ` Rod Taylor <pg@rbt.ca>
2004-06-10 03:08 ` Re: Index oddity Mark Kirkwood <markir@coretech.co.nz>
0 siblings, 1 reply; 12+ messages in thread
From: Rod Taylor @ 2004-06-10 02:17 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: ken <southerland@samsixedd.com>; pgsql-performance
On Wed, 2004-06-09 at 21:45, Christopher Kings-Lynne wrote:
> > If I take away the diagonalSize condition in my query I find that there
> > are 225 rows that satisfy the other conditions. 155 of these have a
> Maybe you should drop your random_page_cost to something less than 4,
> eg. 3 or even 2...
The big problem is a very poor estimate (off by a couple orders of
magnitude). I was hoping someone with more knowledge in fixing those
would jump in.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 21:29 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 23:11 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-10 01:45 ` Re: Index oddity Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-06-10 02:17 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
@ 2004-06-10 03:08 ` Mark Kirkwood <markir@coretech.co.nz>
2004-06-10 03:32 ` Re: Index oddity Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 12+ messages in thread
From: Mark Kirkwood @ 2004-06-10 03:08 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; ken <southerland@samsixedd.com>; pgsql-performance
Rod Taylor wrote:
>
>The big problem is a very poor estimate (off by a couple orders of
>magnitude). I was hoping someone with more knowledge in fixing those
>would jump in.
>
>
>
>
ANALYZE might be producing poor stats due to :
i) many dead tuples or
ii) high proportion of dead tuples in the first few pages of the table
Does a VACUUM FULL followed by ANALYZE change the estimates (or have you
tried this already)?
(p.s. - I probably don't qualify for the 'more knowledge' bit either...)
regards
Mark
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 21:29 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 23:11 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-10 01:45 ` Re: Index oddity Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-06-10 02:17 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-10 03:08 ` Re: Index oddity Mark Kirkwood <markir@coretech.co.nz>
@ 2004-06-10 03:32 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 12+ messages in thread
From: Joshua D. Drake @ 2004-06-10 03:32 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: Rod Taylor <pg@rbt.ca>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; ken <southerland@samsixedd.com>; pgsql-performance
>>
> ANALYZE might be producing poor stats due to :
>
> i) many dead tuples or
> ii) high proportion of dead tuples in the first few pages of the table
>
> Does a VACUUM FULL followed by ANALYZE change the estimates (or have
> you tried this already)?
>
> (p.s. - I probably don't qualify for the 'more knowledge' bit either...)
You can also increase your statistics_target which will make ANALYZE
take longer but can help a great deal
with larger data sets.
Sincerely,
Joshua D. Drake
>
> regards
>
> Mark
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Index oddity
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` Re: Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Re: Index oddity Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` Re: Index oddity ken <southerland@samsixedd.com>
@ 2004-06-10 05:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-06-10 05:01 UTC (permalink / raw)
To: ken <southerland@samsixedd.com>; +Cc: Rod Taylor <pg@rbt.ca>; pgsql-performance
ken <southerland@samsixedd.com> writes:
> ... and here is the plan with statistics set to 1000 ...
> Seq Scan on nrgfeature f (cost=0.00..31675.57 rows=18608 width=218)
> (actual time=63.544..1002.701 rows=225 loops=1)
> Filter: ((upperrightx > 321264.236977215::double precision) AND
> (lowerleftx < 324046.799812083::double precision) AND (upperrighty >
> 123286.261898636::double precision) AND (lowerlefty <
> 124985.927450476::double precision) AND (diagonalsize > 49.999::double
> precision))
> ... so yeah, its obviously finding way, way less rows than it thinks it
> will.
Yup. I think your problem here is that the conditions on the different
columns are highly correlated, but the planner doesn't know anything
about that, because it has no cross-column statistics.
You could check that the individual conditions are accurately estimated
by looking at the estimated and actual row counts in
explain analyze
SELECT * FROM nrgfeature f WHERE upperRightX > 321264.23697721504;
explain analyze
SELECT * FROM nrgfeature f WHERE lowerLeftX < 324046.79981208267;
etc --- but I'll bet lunch that they are right on the money with the
higher statistics targets, and probably not too far off even at the
default. The trouble is that the planner is simply multiplying these
probabilities together to get its estimate for the combined query,
and when the columns are not independent that leads to a very bad
estimate.
In particular it sounds like you have a *whole lot* of rows that have
diagonalSize just under 50, and so changing the diagonalSize condition
to include those makes for a big change in the predicted number of rows,
even though for the specific values of upperRightX and friends in your
test query there isn't any change in the actual number of matching rows.
I don't have any advice to magically solve this problem. I would
suggest experimenting with alternative data representations -- for
example, maybe it would help to store "leftX" and "width" in place
of "leftX" and "rightX", etc. What you want to do is try to decorrelate
the column values. leftX and rightX are likely to have a strong
correlation, but maybe leftX and width don't.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-06-10 05:01 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-09 19:31 Index oddity ken <southerland@samsixedd.com>
2004-06-09 20:12 ` Rod Taylor <pg@rbt.ca>
2004-06-09 20:50 ` ken <southerland@samsixedd.com>
2004-06-09 20:56 ` Rod Taylor <pg@rbt.ca>
2004-06-09 21:02 ` ken <southerland@samsixedd.com>
2004-06-09 21:29 ` Rod Taylor <pg@rbt.ca>
2004-06-09 23:11 ` ken <southerland@samsixedd.com>
2004-06-10 01:45 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-06-10 02:17 ` Rod Taylor <pg@rbt.ca>
2004-06-10 03:08 ` Mark Kirkwood <markir@coretech.co.nz>
2004-06-10 03:32 ` Joshua D. Drake <jd@commandprompt.com>
2004-06-10 05:01 ` Tom Lane <tgl@sss.pgh.pa.us>
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