Re: Serious performance problem

On Mon, 29 Oct 2001, Ross J. Reedstrom wrote:

> Here's what I see: Your example touches on what can be an achilles
> heel for pgsql's current statistical analyzer: selection on data fields
> that have a few common values. Often, the indices don't get used, since
> a large fraction of the table needs to be scanned, in any case. In
> your example, fully 68% of the table fits the where condition.
> ...
>
> I think we have a winner. No it's not sub-second, but I improved the time
> by 3x just by trying some indices. Note that I _still_ had to force the
> use of indices for this one. It's also the first time I've personally seen
> a query/dataset that benefits this much from a two-key index.
This is true for this example and I also played with indices as you. I also
enforced the index scan and compared with forbidding the index scan. The
result was on my more realistic examples that both versions performed quite
the same. There was no *real* difference. For sure in this simple query there
is a difference but the real examples showed only 2% - 5% speed increase
(if not slower with enforcing index scans!).

> As another poster replied to you, there is limitation with postgresql's
> use of indices that arises from MVCC: even if the only data requested is
> that stored in the index itself, the backend must visit the actual tuple
> in the table to ensure that it is 'visible' to the current transaction.
Any possibility to switch of this temporarily for certain queries like this
if the programmer could make sure that it is not necessary? Just a stupid
idea from a bloody uneducated man in database-engeniering.

> How realistic a representation of your real workload is this query? Realize
> that more selective, complex queries are where pgsql shines compared to
> other RDBMS: the 'fast table scanner' type query that you proposed as your
> test don't really let pgsql stretch it's legs. Do you have example timings
> from MS-SQL or others?
Unfortunately the four test we did here seemed all to suffer from the
same problem. The situation is that there is a given database structure
which was developed over more than a year on MS-SQL and has a Access GUI.
Now parts of the UI should be made public via web (I want to use Zope)
and I just imported the data and did some example queries with the
terrible slow result.

Kind regards and thanks for your ideas

Andreas.