Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-04 Thread Arnaud L.
Le 04/09/2019 à 09:04, Arnaud L. a écrit : Le 03/09/2019 à 15:43, Tom Lane a écrit : The planner should be able to do better than that, given up-to-date statistics on the "nodes" column. Tom, I can confirm that with up to date statistics the planner is still lost. I did a REINDEX to rule out a

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-04 Thread Arnaud L.
Le 04/09/2019 à 09:04, Arnaud L. a écrit : Tom, I can confirm that with up to date statistics the planner is still lost. I did a REINDEX to rule out a broken index and the estimate is still in the 100k+ range. Sorry, I meant 1M+ range. EXPLAIN ANALYZE select id from planet_osm_ways WHERE nodes

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-04 Thread Arnaud L.
Le 03/09/2019 à 15:43, Tom Lane a écrit : "Arnaud L." writes: We have upgraded our database from 9.6 to 11 (and updated PostGIS from 2.3 to 2.5 as well). ... Have you re-ANALYZEd the database? The problem with this query seems to be the spectacularly awful rowcount estimate here: -> Bi

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-03 Thread Paul Ramsey
> On Sep 3, 2019, at 7:06 AM, Arnaud L. wrote: > > Le 03/09/2019 à 15:43, Tom Lane a écrit : >> "Arnaud L." writes: >>> We have upgraded our database from 9.6 to 11 (and updated PostGIS from 2.3 >>> to 2.5 as well). >>> ... >> Have you re-ANALYZEd the database? The problem with this query >

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-03 Thread Imre Samu
> We have upgraded our database from 9.6 to 11 This is now the latest PG ? PG11.5? ( for example - in PG11.5 fixed: * "Fix failure to resolve deadlocks involving multiple parallel worker processes"* https://www.postgresql.org/docs/current/release-11-5.html ) > populated by the osm2pgsql program

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-03 Thread Arnaud L.
Le 03/09/2019 à 17:02, Imre Samu a écrit : > We have upgraded our database from 9.6 to 11 This is now the latest PG ?   PG11.5? Yes, PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit. - the osm2pgsql has an own parallelizations ...  ( osm2pgsql --number-processes .. )    so be c

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-03 Thread Arnaud L.
Le 03/09/2019 à 16:39, Paul Ramsey a écrit : On Sep 3, 2019, at 7:06 AM, Arnaud L. wrote: I've setup parallel_tuple_cost to 1.0 parallel_setup_cost to 5000.0 for the time being which solves this specific problem. These value don't look very sensible though, they are very high compared to the d

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-03 Thread Arnaud L.
Le 03/09/2019 à 15:43, Tom Lane a écrit : "Arnaud L." writes: We have upgraded our database from 9.6 to 11 (and updated PostGIS from 2.3 to 2.5 as well). ... Have you re-ANALYZEd the database? The problem with this query seems to be the spectacularly awful rowcount estimate here: You mean

Re: Slow statement using parallelism after 9.6>11 upgrade

2019-09-03 Thread Tom Lane
"Arnaud L." writes: > We have upgraded our database from 9.6 to 11 (and updated PostGIS from > 2.3 to 2.5 as well). > ... Have you re-ANALYZEd the database? The problem with this query seems to be the spectacularly awful rowcount estimate here: >-> Bitmap Index Scan on planet_osm_ways_nod

Slow statement using parallelism after 9.6>11 upgrade

2019-09-03 Thread Arnaud L.
Hi list We have upgraded our database from 9.6 to 11 (and updated PostGIS from 2.3 to 2.5 as well). We are using it among other things to store an OpenStreetMap database, populated by the osm2pgsql program and updated on a daily basis. The process used to take ~1h30 minutes before the upgrade