From: "Tom Lane" <[EMAIL PROTECTED]>
> Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> > We have a table with over 1 million rows and the statistics Postgres
gathers
> > are not particularly useful. There is not one (non-null) value that
occurs
> > significantly more often than other values
Ian Lance Taylor wrote:
>
> You're right. The mechanism used to preserve multiple versions of
> heap tuples could be extended to index tuples as well.
>
> Based on the heap tuple implementation, this would require adding two
> transaction ID's and a few flags to each index tuple. That's not
> > Also, more work would be required for every update. Right now an
> > update requires a B-tree insert for each index. With this change,
> > every update would require an additional B-tree lookup and write for
> > each index. That would require on average a bit less than one
> > additional bl
On Wed, 21 Feb 2001, Martijn van Oosterhout wrote:
> On Tue, Feb 20, 2001 at 05:02:22PM -0800, Stephan Szabo wrote:
> >
> > IIRC, There's something which is effectively :
> > estimated rows = *
> > I think fraction defaults to (is always?) 1/10 for the standard
> > index type. That's where the
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> > Note that this all implies that when walking through the index to find
> > heap tuples, you must check the current validity of each heap tuple.
> > It is normal for an index tuple to point to a heap tuple which has
> > been deleted.
>
>
> > >
> >
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> Why? There is a mechanism for keeping track of which heap tuples are
> valid, why not index tuples? It is the nature of indices to be updated
> on inserts, why not deletes?
An index is a hint: these tuples *might* be of interest to your
transaction
Ian Lance Taylor wrote:
>
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
> > > > I understand that keeping different views for different open
> > > > transactions can be difficult, but after a transaction that updates a
> > > > row is over why isn't the row marked as 'universally visible' for
On Tue, Feb 20, 2001 at 05:02:22PM -0800, Stephan Szabo wrote:
>
> IIRC, There's something which is effectively :
> estimated rows = *
> I think fraction defaults to (is always?) 1/10 for the standard
> index type. That's where the 50 comes from. And the frequency is
> probably from the last vac
On Tue, 20 Feb 2001, Joseph Shraibman wrote:
> Err I wan't complaing about count(*) per se, I was just using that as a
> simple example of something that should be done with an index. Because
> if the index doesn't have to worry about rows that aren't current then
> you don't even have to go in
Stephan Szabo wrote:
>
> On Tue, 20 Feb 2001, Joseph Shraibman wrote:
>
> > Stephan Szabo wrote:
> >
> > > Where are you seeing something that says the estimator/planner using the
> > > index to get an upper bound? The estimator shouldn't be asking either the
> > > index or the heap for anythin
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> > > I understand that keeping different views for different open
> > > transactions can be difficult, but after a transaction that updates a
> > > row is over why isn't the row marked as 'universally visible' for all
> > > new transactions until ano
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> Then it should do the same thing no matter what value I use, but when I
> do different searches in one case it estimates 50 when there are 16 and
> in the other it estimeates 502 where there are 502.
Well, it does know the difference between searchin
Err I wan't complaing about count(*) per se, I was just using that as a
simple example of something that should be done with an index. Because
if the index doesn't have to worry about rows that aren't current then
you don't even have to go into the heap because the index alone should
have enough
On Tue, 20 Feb 2001, Joseph Shraibman wrote:
> > That's because the estimate in this case was 50 and so it's estimating
> > that going through the index and checking the heap is faster than a
> > sequence scan. The *estimator* didn't use the index to figure that out,
> > it's just saying that t
Ian Lance Taylor wrote:
>
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
> A caveat on this reply: I've been studying the Postgres internals, but
> I have not mastered them.
>
> > I understand that keeping different views for different open
> > transactions can be difficult, but after a trans
Joseph Shraibman <[EMAIL PROTECTED]> writes:
A caveat on this reply: I've been studying the Postgres internals, but
I have not mastered them.
> I understand that keeping different views for different open
> transactions can be difficult, but after a transaction that updates a
> row is over why
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> Maybe I'm not making myself understood. Another way of asking the same
> thing:
> Say there is a transaction that is looking at a non-current version of a
> row. 'non-current' could be the value it was at the start of the
> transaction (and was upda
On Mon, 19 Feb 2001, Joseph Shraibman wrote:
> > Of course, if the 10113-match estimate is wildly off (as it was in this
> > case), then the wrong plan may be chosen. But it IS NOT CORRECT to
> > suppose that indexscans always beat seqscans. The planner's job would
> > be a lot easier if that
Joseph Shraibman wrote:
>
> Can't postgres do the index lookup first and find out there are only a
> few tuples that might match?
>
Actually it looks like postgres is doing this:
o=# explain select * from usertable where p = 33;
NOTICE: QUERY PLAN:
Seq Scan on usertable (cost=0.00..30.54 r
Tom Lane wrote:
>
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
> > Stephan Szabo wrote:
> >> Do you have a value that is not null that is very common?
> >> It's estimating that there will be 10113 rows that match
> >> nomsession='xxx' which makes a seq scan a much less bad plan.
> >>
> > Err, w
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> Stephan Szabo wrote:
>> Do you have a value that is not null that is very common?
>> It's estimating that there will be 10113 rows that match
>> nomsession='xxx' which makes a seq scan a much less bad plan.
>>
> Err, why? There is an index, isn't th
On Mon, 19 Feb 2001, Joseph Shraibman wrote:
> Stephan Szabo wrote:
> >
> > Do you have a value that is not null that is very common?
> > It's estimating that there will be 10113 rows that match
> > nomsession='xxx' which makes a seq scan a much less bad plan.
> >
> Err, why? There is an inde
Stephan Szabo wrote:
>
> Do you have a value that is not null that is very common?
> It's estimating that there will be 10113 rows that match
> nomsession='xxx' which makes a seq scan a much less bad plan.
>
Err, why? There is an index, isn't there? Shouldn't the index allow
postgres to quickl
23 matches
Mail list logo
