Re: Stupid index idea...

There is no backward link from a heap tuple to it's index entries. So if
you have 3 indexes on a table and do an update, you need at least 2 more
index lookups just to set that bit, if you somehow manage to remember by
what index you found this heap tuple in the first place.

On update-heavy tables this will degrade performance quite a bit,
wouldn't it? And I don't know offhand into what concurrency problems we
run to actually clear that bit. Who is responsible for clearing it and
what are the criteria for doing so anyway?

Jan

Mario Weilguni wrote:

> Hi,
>
> probably it's just a stupid idea, but what do you think of this:currently,
> most if not all queries with aggregates (count(), sum()....) make seq scans
> when there are no conditions at all. especially count() is a classic question
> on any postgres list.
>
> the reason is - at least that's what I got from this list - is the
> multi-version system postgres is using. Tom Lane once said that resolving
> this to use index scans would mean adding 8 bytes to every index tuples,
> which is no way to go. but what if 1 bit could be added to index items,
> indication that the heap tuples MAY be modified?
>
> in any of my database applications updated tuples in the tables are seldom,
> because of vacuum. I've a table containing 10 million of tuples, and a
> count(1) on them takes several minutes. however, the table is quite static
> and changes seldom, and only a few items change.
>
> so an extra bit in the index could indicate: this is a candiate, but the heap
> must be checked. in most of my applications this would mean that for 95% of
> all tuples the index can be used for aggregates, only the remaining 5% of all
> tuples that MIGHT be modified or deleted have to be checked via heap.
> and even those 5% are often too much, in my case it's typically below 1%.
>
> The statistic collector could even tell the optimizer if an index scan is the
> way to go...
>
> Would that work?
>
> Best regards,
> Mario Weilguni
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #