ken <[EMAIL PROTECTED]> writes:
> Is there something wrong with rtree indexes on box data types?
Not per se, but the selectivity estimator for && is just a stub :-(.
Feel free to take a stab at improving this, or take a look at PostGIS
--- I think those guys are working the same problem even now.
Apologies in advance for the length of this post but I want to be as
thorough as possible in describing my problem to avoid too much banter
back and forth.
First off, thanks to all for your help with my index problem on my
multi-column index made up of 5 double precision columns.
Unfortunately, I
ken <[EMAIL PROTECTED]> 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 <
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' bi
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 page
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
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
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
> ... 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 preci
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, m
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
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 wro
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 ALTER COLUMN diagonalsize SET STATISTICS 1000;
ANALZYE ;
Send back EXPLAIN ANALYZE output for the >49.99
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
T
14 matches
Mail list logo
