Thank you very much. I catch the point why it is done so.
But I'm curious whether it is still possible to don't fetch data from
files table just because inappropriate ids (e.g. removed ones) will
not produce any wrong effect just because them indirectly "checked" on
downloads table?
Here I mean that if we get id (from index) for file which is actually
removed, then we will not find anything in downloads table.
Probably my knowledge about MVCC is too little to see whole picture,
so if it is not hard to you please point the "failure" scenario (when
we get wrong result) or locking issue, ...
Michael Mikhulya
> Unfortunately, the index does not contain enough information to accomplish
> this. This is due to Postgres' advanced concurrency control system. Postgres
> needs to fetch the actual rows from the files table in order to check
> whether that row is visible in the current transaction, and a Bitmap Index
> Scan is the fastest way to do this.
>
> You can speed this up in Postgres 8.4 by having a RAID array and setting the
> effective_concurrency configuration to the number of spindles in the RAID
> array, or by having gobs of RAM and keeping everything in cache.
>
> Matthew
>
> --
> A good programmer is one who looks both ways before crossing a one-way
> street.
> Considering the quality and quantity of one-way streets in Cambridge, it
> should be no surprise that there are so many good programmers there.
>