On 11/12/15 19:18, Marco Nenciarini wrote: > On 11/12/15 18:48, Alvaro Herrera wrote: >> Hi, >> >> A customer of ours hit some very slow code while running the >> @>(polygon, polygon) operator with some big polygons. I'm not familiar >> with this stuff but I think the problem is that the algorithm converges >> too slowly to a solution and also has some pretty expensive calls >> somewhere. (Perhaps there is also a problem that the algorithm *never* >> converges for some inputs ...) >> >> While I'm not familiar with the code itself, and can't post the exact >> slow query just yet, I have noticed that it is missing a >> CHECK_FOR_INTERRUPTS() call to enable cancelling the slow query. I'd >> backpatch this all the way back. (The exact issue they hit is mutual >> recursion between touched_lseg_between_poly and lseg_between_poly. >> Since the latter also recurses on itself, the best way forward seem to >> add a check for interrupts in the loop there.) >> >> I will follow up on the actual slowness later, as warranted. >> > > I would add that it was not simply a slow computation, but more probably they > hit a case where the algorithm doesn't converge at all. > > I've killed it manually by calling ProcessInterrupts() through gdb after 7 > days and half of CPU time (100% of one CPU). > The server CPU is an Intel(R) Xeon(R) CPU E5-2660 v2 @ 2.20GHz. > > The query doesn't involve any table and is a simple call of @>(polygon, > polygon) operator. > > SELECT polygon 'poligon literal with 522 points' @> polygon 'poligon box' > > I'm checking if we can share the full query. >
The full query is attached. Regards, Marco -- Marco Nenciarini - 2ndQuadrant Italy PostgreSQL Training, Services and Support marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it
SELECT polygon '((-880000000,419013470),(-880000000,419007240),(-880000000,419007240),(-880000000,418987250),(-880000000,418987250),(-880000000,418970240),(-880000000,418970240),(-880000000,418950990),(-880000000,418950990),(-880000000,418923770),(-880000000,418923770),(-880000000,418917630),(-880000000,418917630),(-880000000,418912100),(-880000000,418912100),(-880000000,418900410),(-880000000,418900410),(-880000000,418895420),(-880000000,418895420),(-880000000,418893450),(-880000000,418893450),(-880000000,418859340),(-880000000,418859340),(-880000000,418842280),(-880000000,418842280),(-880000000,418824820),(-880000000,418824820),(-880000000,418819820),(-880000000,418819820),(-880000000,418812320),(-880000000,418812320),(-880000000,418781690),(-880000000,418781690),(-880000000,418750360),(-880000000,418750360),(-880000000,418750000),(-880000000,418750000),(-879957280,418750000),(-879957280,418750000),(-879946700,418750000),(-879946700,418750000),(-879932880,418750000),(-879932880,418750000),(-879921140,418750000),(-879921140,418750000),(-879901800,418750000),(-879901800,418750000),(-879884730,418750000),(-879884730,418750000),(-879869770,418750000),(-879869770,418750000),(-879853620,418750000),(-879853620,418750000),(-879838680,418750000),(-879838680,418750000),(-879820670,418750000),(-879820670,418750000),(-879803830,418750000),(-879803830,418750000),(-879788600,418750000),(-879788600,418750000),(-879771820,418750000),(-879771820,418750000),(-879755740,418750000),(-879755740,418750000),(-879738740,418750000),(-879738740,418750000),(-879738580,418750000),(-879738580,418750000),(-879702620,418750000),(-879702620,418750000),(-879687560,418750000),(-879687560,418750000),(-879617590,418750000),(-879617590,418750000),(-879614270,418750000),(-879614270,418750000),(-879611560,418750000),(-89611560,418750000),(-879551870,418750000),(-879551870,418750000),(-879544600,418750000),(-879544600,418750000),(-879531980,418750000),(-879531980,418750000),(-879519330,418750000),(-879519330,418750000),(-879507350,418750000),(-879507350,418750000),(-879494370,418750000),(-879494370,418750000),(-879481570,418750000),(-879481570,418750000),(-879469050,418750000),(-879469050,418750000),(-879457200,418750000),(-879457200,418750000),(-879446660,418750000),(-879446660,418750000),(-879435070,418750000),(-879435070,418750000),(-879423630,418750000),(-879423630,418750000),(-879411030,418750000),(-879411030,418750000),(-879397520,418750000),(-879397520,418750000),(-879385310,418750000),(-879385310,418750000),(-879364550,418750000),(-879364550,418750000),(-879347030,418750000),(-879347030,418750000),(-879336090,418750000),(-879336090,418750000),(-879323380,418750000),(-879323380,418750000),(-87932310,418750000),(-879312310,418750000),(-879300290,418750000),(-879300290,418750000),(-879288280,418750000),(-879288280,418750000),(-879256170,418750000),(-879256170,418750000),(-879245250,418750000),(-879245250,418750000),(-879230540,418750000),(-879230540,418750000),(-879223070,418750000),(-879223070,418750000),(-879204650,418750000),(-879204650,418750000),(-879191420,418750000),(-879191420,418750000),(-879184480,418750000),(-879184480,418750000),(-879166660,418750000),(-879166660,418750000),(-879143370,418750000),(-879143370,418750000),(-879130620,418750000),(-879130620,418750000),(-879124800,418750000),(-879124800,418750000),(-879102120,418750000),(-879102120,418750000),(-879089020,418750000),(-879089020,418750000),(-879074480,418750000),(-879074480,418750000),(-879061510,418750000),(-879061510,418750000),(-879050380,418750000),(-879050380,418750000),(-879037450,418750000),(-87903740,418750000),(-879024420,418750000),(-879024420,418750000),(-879011310,418750000),(-879011310,418750000),(-878996680,418750000),(-878996680,418750000),(-878984750,418750000),(-878984750,418750000),(-878973830,418750000),(-878973830,418750000),(-878971200,418750000),(-878971200,418750000),(-878960420,418750000),(-878960420,418750000),(-878950440,418750000),(-878950440,418750000),(-878941460,418750000),(-878941460,418750000),(-878938940,418750000),(-878938940,418750000),(-878926680,418750000),(-878926680,418750000),(-878829580,418750000),(-878829580,418750000),(-878795610,418750000),(-878795610,418750000),(-878788760,418750000),(-878788760,418750000),(-878779580,418750000),(-878779580,418750000),(-878754070,418750000),(-878754070,418750000),(-878750000,418750000),(-878750000,418750000),(-878750000,418752180),(-878750000,418752180),(-878750000,418760470),(-878750000,418760470),(-878750000,18769720),(-878750000,418769720),(-878750000,418779190),(-878750000,418779190),(-878750000,418786480),(-878750000,418786480),(-878750000,418790300),(-878750000,418790300),(-878750000,418798740),(-878750000,418798740),(-878750000,418816300),(-878750000,418816300),(-878750000,418844860),(-878750000,418844860),(-878750000,418865960),(-878750000,418865960),(-878750000,418883640),(-878750000,418883640),(-878750000,418908300),(-878750000,418908300),(-878750000,418970530),(-878750000,418970530),(-878750000,418981300),(-878750000,418981300),(-878750000,418999260),(-878750000,418999260),(-878750000,419033610),(-878750000,419033610),(-878750000,419054300),(-878750000,419054300),(-878750000,419061710),(-878750000,419061710),(-878750000,419073300),(-878750000,419073300),(-878750000,419075570),(-878750000,419075570),(-878750000,419107300),(-878750000,419107300),(-878750000,419147630),(-878750000,41947630),(-878750000,419183810),(-878750000,419183810),(-878750000,419193030),(-878750000,419193030),(-878750000,419211120),(-878750000,419211120),(-878750000,419221010),(-878750000,419221010),(-878750000,419249210),(-878750000,419249210),(-878750000,419258180),(-878750000,419258180),(-878750000,419274580),(-878750000,419274580),(-878750000,419280160),(-878750000,419280160),(-878750000,419282060),(-878750000,419282060),(-878750000,419283210),(-878750000,419283210),(-878750000,419298260),(-878750000,419298260),(-878750000,419310570),(-878750000,419310570),(-878750000,419328300),(-878750000,419328300),(-878750000,419346300),(-878750000,419346300),(-878750000,419362580),(-878750000,419362580),(-878750000,419379090),(-878750000,419379090),(-878750000,419394980),(-878750000,419394980),(-878750000,419401000),(-878750000,419401000),(-878750000,419419300),(-878750000,419419300),(-878750000,419437300),(-878750000,419437300),(-878750000,419453300),(-878750000,419453300),(-878750000,419472300),(-878750000,419472300),(-878750000,419498300),(-878750000,419498300),(-878750000,419559040),(-878750000,419559040),(-878750000,419582630),(-878750000,419582630),(-878750000,419598600),(-878750000,419598600),(-878750000,419622410),(-878750000,419622410),(-878750000,419626020),(-878750000,419626020),(-878750000,419640970),(-878750000,419640970),(-878750000,419655930),(-878750000,419655930),(-878750000,419730790),(-878750000,419730790),(-878750000,419823550),(-878750000,419823550),(-878750000,419823890),(-878750000,419823890),(-878750000,419898030),(-878750000,419898030),(-878750000,419916320),(-878750000,419916320),(-878750000,419935610),(-878750000,419935610),(-878750000,419950660),(-878750000,419950660),(-878750000,419957710),(-878750000,419957710),(-878750000,419959690),(-878750000,419959690),(-878750000,419988310),(-878750000,419988310),(-878750000,419997300),(-878750000,419997300),(-878750000,420000000),(-878750000,420000000),(-878796170,420000000),(-878796170,420000000),(-878818300,420000000),(-878818300,420000000),(-878820850,420000000),(-878820850,420000000),(-878823480,420000000),(-878823480,420000000),(-878824760,420000000),(-878824760,420000000),(-878846130,420000000),(-878846130,420000000),(-878940170,420000000),(-878940170,420000000),(-878957520,420000000),(-878957520,420000000),(-879050000,420000000),(-879050000,420000000),(-879050680,420000000),(-879050680,420000000),(-879198660,420000000),(-879198660,420000000),(-879353110,420000000),(-879353110,420000000),(-879401990,420000000),(-879401990,420000000),(-879419650,420000000),(-879419650,420000000),(-879426990,420000000),(-879426990,420000000),(-879493560,420000000),(-879493560,420000000),(-879551870,420000000),(-879551870,420000000),(-879593170,420000000),(-879593170,420000000),(-879697840,420000000),(-879697840,420000000),(-879774750,420000000),(-879774750,420000000),(-879776710,420000000),(-879776710,420000000),(-879787770,420000000),(-879787770,420000000),(-879793080,420000000),(-879793080,420000000),(-879800680,420000000),(-879800680,420000000),(-879819820,420000000),(-879819820,420000000),(-879829680,420000000),(-879829680,420000000),(-879843500,420000000),(-879843500,420000000),(-879851680,420000000),(-879851680,420000000),(-879862570,420000000),(-879862570,420000000),(-879872680,420000000),(-879872680,420000000),(-879882290,420000000),(-879882290,420000000),(-879900290,420000000),(-879900290,420000000),(-879916020,420000000),(-879916020,420000000),(-879922620,420000000),(-879922620,420000000),(-879928780,420000000),(-879928780,420000000),(-879941520,420000000),(-879941520,420000000),(-879976180,420000000),(-879976180,420000000),(-880000000,420000000),(-880000000,420000000),(-880000000,419999750),(-880000000,419999750),(-880000000,419991890),(-880000000,419991890),(-880000000,419984270),(-880000000,419984270),(-880000000,419976010),(-880000000,419976010),(-880000000,419968610),(-880000000,419968610),(-880000000,419960980),(-880000000,419960980),(-880000000,419952750),(-880000000,419952750),(-880000000,419946490),(-880000000,419946490),(-880000000,419928250),(-880000000,419928250),(-880000000,419880720),(-880000000,419880720),(-880000000,419836270),(-880000000,419836270),(-880000000,419806440),(-880000000,419806440),(-880000000,419799470),(-880000000,419799470),(-880000000,419788040),(-880000000,419788040),(-880000000,419720125),(-880000000,419720125),(-880000000,419713973),(-880000000,419713973),(-880000000,419698630),(-880000000,419698630),(-880000000,419690110),(-880000000,419690110),(-880000000,419668360),(-880000000,419668360),(-880000000,419655290),(-880000000,419655290),(-880000000,419634540),(-880000000,419634540),(-880000000,419626370),(-880000000,419626370),(-880000000,419605310),(-880000000,419605310),(-880000000,419596260),(-880000000,419596260),(-880000000,419588180),(-880000000,419588180),(-880000000,419579330),(-880000000,419579330),(-880000000,419569810),(-880000000,419569810),(-880000000,419560780),(-880000000,419560780),(-880000000,419551140),(-880000000,419551140),(-880000000,419543610),(-880000000,419543610),(-880000000,419533820),(-880000000,419533820),(-880000000,419524880),(-880000000,419524880),(-880000000,419475170),(-880000000,419475170),(-880000000,419447260),(-880000000,419447260),(-880000000,419423860),(-880000000,419423860),(-880000000,419414890),(-880000000,419414890),(-880000000,419369550),(-880000000,419369550),(-880000000,419335010),(-880000000,419335010),(-880000000,419322290),(-880000000,419322290),(-880000000,419313350),(-880000000,419313350),(-880000000,419306580),(-880000000,419306580),(-880000000,419290310),(-880000000,419290310),(-880000000,419289890),(-880000000,419289890),(-880000000,419272270),(-880000000,419272270),(-880000000,419264970),(-880000000,419264970),(-880000000,419255280),(-880000000,419255280),(-880000000,419240670),(-880000000,419240670),(-880000000,419236360),(-880000000,419236360),(-880000000,419221620),(-880000000,419221620),(-880000000,419218290),(-880000000,419218290),(-880000000,419195290),(-880000000,419195290),(-880000000,419154230),(-880000000,419154230),(-880000000,419142830),(-880000000,419142830),(-880000000,419133270),(-880000000,419133270),(-880000000,419107450),(-880000000,419107450),(-880000000,419087030),(-880000000,419087030),(-880000000,419077760),(-880000000,419077760),(-880000000,419077280),(-880000000,419077280),(-880000000,419064320),(-880000000,419064320),(-880000000,419056150),(-880000000,419056150),(-880000000,419048300),(-880000000,419048300),(-880000000,419034850),(-880000000,419034850),(-880000000,419023420),(-880000000,419023420),(-880000000,419013470))' @> polygon '((-880000000,418750000),(-878750000,418750000),(-878750000,420000000),(-880000000,420000000))' as containsPolygon ;
signature.asc
Description: OpenPGP digital signature
