SELECT FOR SHARE
Hello!
I'm interested in SELECT ... FOR SHARE 8.1 feature. I'd tried to measure performance degradation and got something about 30-60% for queries retrieve a lot of rows. Is it realistic estimation? Does this penalty depend on something (data types, triggers, foreign keys, whatever) significantly? Is any sort of lock escalation [and other unpreditable things] involved here?
Last question. What about ISOLATION LEVEL REPEATABLE READ syntax since non-exclusive locking for all SELECTs means [as far as I understand] nothing but REPEATABLE READ?
Thanks.
--
Best regards
Ilja Golshtein
"Ilja Golshtein" <ilejn@yandex.ru> writes:
I'm interested in SELECT ... FOR SHARE 8.1 feature. I'd tried to
measure performance degradation and got something about 30-60% for
queries retrieve a lot of rows.
Degradation relative to what?
regards, tom lane
"Ilja Golshtein" <ilejn@yandex.ru> writes:
I'm interested in SELECT ... FOR SHARE 8.1 feature. I'd tried to
measure performance degradation and got something about 30-60% for
queries retrieve a lot of rows.Degradation relative to what?
Thanks for response and sorry for bad formulating.
I've compared queries (SELECTs) with and without FOR SHARE
clause. I think it was CREATE TABLE AS (SELECT ... FOR SHARE)
versus CREATE TABLE AS (SELECT ...).
The same box, same PG, same data.
As far I remember (protocol in office),
if retrive all 10000 records from a table
with some small CHARs and INTs, SELECT FOR SHARE is about 60%
slower then the same SELECT.
If query is complex and it retrieves (and locks) few rows,
difference is not so significant.
No surprise at all.
I'm asking because not sure my tests are exhaustive
and the worst case is covered.
--
Best regards
Ilja Golshtein