Re: table full scan or index full scan?

2009/10/11 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
>> The postgresql database uses the table full scan.but in oracle, the similar SQL uses the index full scanning,speed quickly many than postgresql.
>
> Yep, PostgreSQL isn't Oracle.  It's a trade off.  In pgsql indexes
> don't contain visibility info, so all index lookups have to eventually
> hit the table itself.  So you either do indexlookup -> table lookup,
> repeat as many times as you have index lookups or you just hit the
> table since you gotta go there anyway.
>
> On the  bright side, this makes updates faster since you don't have to
> lock both table and index and write to both at the same time anymore.
>
>> postgresql's optimizer whether to have the necessity to make the adjustment?
>
> Sorry, it's an architectural difference.  Are you testing in a
> realistic scenario including both reads and writes to the database to
> see if postgresql is faster overall and identify problem areas that
> pop up there?
>

This is interesting, I just ran a similar issue the other day.
Clearly there is a wide range of read / write scenarios that Postgres
should be able to cover. These days, I have a lot of designs leaning
more toward the data warehouse side of the operational spectrum as
opposed to the high transaction scenario and I specifically design DB
management strategies with the knowledge that writes will happen far
less than reads in our applications. Is this an area where
optimizations are considered hard in Postrgres or hopefully, just
something that is on the todo list but just no one has gotten around
to yet? Similarly, are accurate table summary stats possible someday
or are they considered close to impossible in order to eliminate race
conditions and lock contention scenarios?

--
Peter Hunsberger