Re: Serious performance problem
От | Tille, Andreas |
---|---|
Тема | Re: Serious performance problem |
Дата | |
Msg-id | Pine.LNX.4.33.0110301042200.6117-100000@wr-linux02.rki.ivbb.bund.de обсуждение исходный текст |
Ответ на | Re: Serious performance problem ("Ross J. Reedstrom" <reedstrm@rice.edu>) |
Список | pgsql-hackers |
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.
В списке pgsql-hackers по дате отправления: