Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Sun, May 27, 2012 at 1:45 PM, Sergey Koposov <koposov(at)ast(dot)cam(dot)ac(dot)uk> wrote:
> Hi,
>
> I did another test using the same data and the same code, which I've
> provided before and the performance of the single thread seems to be
> degrading quadratically with the number of threads.
>
> Here are the results:
> Nthreads Time_to_execute_one_thread
> 1 8.1
> 2 7.8
> 3 8.1
> 4 9.0
> 5 10.2
> 6 11.4
> 7 13.3
> 8 16.1
> 9 19.0
> 10 21.4
> 11 23.8
> 12 27.3
> 13 30.2
> 14 32.0
> 15 34.1
> 16 37.5

Ok, I double checked offline with Sergey to make sure the strategy
wasn't helping...it isn't, and we confirmed it was being forced on
after playing with the tunables a bit. So what is happening here? I
have a hunch that this particular query is defeating the ring buffer
strategy code. Here's the query:

create table _tmp0 as select * from (
select *, (select healpixid from idt_match as m where
m.transitid=o.transitid)
as x from idt_photoobservation as o offset 0
) as y where x%16=ZZZ order by x;

(where ZZZ is some number integer number 0<=ZZZ<16)

With the following plan:
----------------------------------------------------------------------------------------------------------------------
Sort (cost=3228814504.96..3228815137.21 rows=252902 width=498)
Sort Key: y.x
-> Subquery Scan on y (cost=0.00..3228791809.25 rows=252902 width=498)
Filter: ((y.x % 16::bigint) = 0)
-> Limit (cost=0.00..3228033102.41 rows=50580456 width=490)
-> Seq Scan on idt_photoobservation o
(cost=0.00..3228033102.41 rows=50580456 width=490)
SubPlan 1
-> Index Scan using idt_match_transitid_idx on
idt_match m (cost=0.00..63.74 rows=1 width=8)
Index Cond: (transitid = o.transitid)

If I understand this properly,.Sergey is scanning a large table and
looking up an integer value from a smaller table row by row and
explicitly forcing it as such (via 'offset 0'). That integer value is
then used to filter the resultant table down to size 1/N so that
multiple workers can process the data which is then sorted. This
isn't a particularly great way to attack the problem, but it should
scale better than it does. I think the problem is coming not from the
main seq scan, but from the dependent index lookup on idt_match. Even
though the scan can be made to exhibit lock free behavior, the index
lookups will eventually start lining up and hit the same sequences of
pages in the same order -- bam.

merlin