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 but the
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 = most common value's frequency*fraction
I think fraction defaults to (is always?) 1/10 for the standard
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 block write
Ian Lance Taylor wrote:
snip
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
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 updated by
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
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 transaction
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 the best
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
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 searching
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 another update
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 anything, it should be
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 into
On Tue, Feb 20, 2001 at 05:02:22PM -0800, Stephan Szabo wrote:
IIRC, There's something which is effectively :
estimated rows = most common value's frequency*fraction
I think fraction defaults to (is always?) 1/10 for the standard
index type. That's where the 50 comes from. And the
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 all
new
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.
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.
snip
I'm
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 quickly
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 index, isn't
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 there?
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
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 were
22 matches
Mail list logo
