Thread: SELECT FOR SHARE

Hello!

I'm interested in SELECT ... FOR SHARE 8.1 feature. I'd tried to measure performance degradation and got something
about30-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]
involvedhere? 

Last question. What about ISOLATION LEVEL REPEATABLE READ syntax since non-exclusive locking for all SELECTs means [as
faras 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