On Sun, May 27, 2012 at 1:45 PM, Sergey Koposov <kopo...@ast.cam.ac.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers