Comments (6)
Algunenano
commented on July 18, 2024
I've created a spreadsheet with information about the different analysis: https://docs.google.com/a/cartodb.com/spreadsheets/d/1f8GuEAEXKZ6UUicrm2OjXbKI-j69MjfrSgd98JydYnw/edit?usp=sharing
Right now I don't know which ones are slower and might require more attention, so I'll be filling blanks as I see fit. I'm using my parallel branches for those projects where they haven't been merged yet
cc @CartoDB/core
from camshaft.
Algunenano
commented on July 18, 2024
I might have hit a big issue we didn't take into account before: All the analysis queries that use cache by outputting to a table like analysis_$hash (which are most of them) use an INSERT query like this:
/* 95592e16-c084-4abd-bd4b-756f17509c43 */ /* analysis:closest */
BEGIN;
DELETE FROM analysis_4bd65e58e4_ba739e4ed70fa2c53826e8bd5a002c1865970ab1;
INSERT INTO analysis_4bd65e58e4_ba739e4ed70fa2c53826e8bd5a002c1865970ab1 WITH
source as(SELECT * FROM my_table),
target as(SELECT * FROM punts_wifi_1)
SELECT
row_number() over() as cartodb_id,
t.the_geom, t.the_geom_webmercator, t.telefon, t.adreca, t.nom_barri, t.nom_districte, t.barri, t.districte, t.equipament, t.latitude, t.longitude, t.etrs89_coord_y, t.etrs89_coord_x, t.ed50_coord_y, t.ed50_coord_x, t.nom_capa, t.capa_generica, t.codi_capa,
source.cartodb_id as source_cartodb_id,
t.cartodb_id as target_cartodb_id,
ST_Distance(geography(t.the_geom), geography(source.the_geom)) as closest_dist
FROM source
CROSS JOIN LATERAL (
SELECT * FROM (
SELECT *,
row_number() over(
ORDER BY source.the_geom <-> the_geom
) AS ranking
FROM target
) AS ranked
WHERE ranking <= 1
) AS t;
SELECT CDB_CheckAnalysisQuota('analysis_4bd65e58e4_ba739e4ed70fa2c53826e8bd5a002c1865970ab1');
ANALYZE analysis_4bd65e58e4_ba739e4ed70fa2c53826e8bd5a002c1865970ab1;
COMMIT;Since an INSERT is done in a top level node (doc) it's making impossible to have a Gather node thus forcing a serial plan.
This is supposed to be a limitation that might change in the future according to the documentation:
The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within a CTE, no parallel plans for that query will be generated. This is a limitation of the current implementation which could be lifted in a future release.
from camshaft.
Algunenano
commented on July 18, 2024
I'm testing a patch already in postgresql/HEAD (coming from this thread) that makes CREATE TABLE AS parallelizable:
cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# SET force_parallel_mode = ON;
SET
cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# EXPLAIN CREATE TABLE oooo AS Select * from analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1153.48 rows=1068 width=246)
Workers Planned: 1
Single Copy: true
-> Seq Scan on analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a (cost=0.00..46.68 rows=1068 width=246)
(4 rows)This would allow to use long analysis queries in a parallel way, but there is several things to take into account:
- Instead of CREATE the table first and then INSERT into it, analysis would need to create the table directly with CREATE TABLE analysis_$hash AS.
- This will only work if the internal function is already parallelizable, so some of the queries would need to be optimized:
For example, an example second layer intersection analysis query look like this:
INSERT INTO analysis_b194a8f896_782928bfb14941a2d63f363041868eed044e3681 SELECT _cdb_analysis_source.cartodb_id, _cdb_analysis_source.the_geom, _cdb_analysis_source.the_geom_webmercator, _cdb_analysis_source.magnst, _cdb_analysis_source.nst, _cdb_analysis_source.magsource, _cdb_analysis_source.locationsource, _cdb_analysis_source.status, _cdb_analysis_source.type, _cdb_analysis_source.place, _cdb_analysis_source.id, _cdb_analysis_source.net, _cdb_analysis_source.magtype, _cdb_analysis_source.magerror, _cdb_analysis_source.deptherror, _cdb_analysis_source.horizontalerror, _cdb_analysis_source.rms, _cdb_analysis_source.dmin, _cdb_analysis_source.gap, _cdb_analysis_source.mag, _cdb_analysis_source.depth, _cdb_analysis_source.longitude, _cdb_analysis_source.latitude, _cdb_analysis_source.updated, _cdb_analysis_source.time, count(_cdb_analysis_target.cartodb_id) as count_vals, count(_cdb_analysis_target.cartodb_id) / GREATEST(0.0000026, ST_Area((ST_Transform(_cdb_analysis_source.the_geom, 4326))::geography)) as count_vals_density
FROM (select * from analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a) _cdb_analysis_source,
(SELECT * FROM punts_wifi_1) _cdb_analysis_target
WHERE ST_Intersects(_cdb_analysis_source.the_geom, _cdb_analysis_target.the_geom)
GROUP BY _cdb_analysis_source.cartodb_id, _cdb_analysis_source.the_geom, _cdb_analysis_source.the_geom_webmercator, _cdb_analysis_source.magnst, _cdb_analysis_source.nst, _cdb_analysis_source.magsource, _cdb_analysis_source.locationsource, _cdb_analysis_source.status, _cdb_analysis_source.type, _cdb_analysis_source.place, _cdb_analysis_source.id, _cdb_analysis_source.net, _cdb_analysis_source.magtype, _cdb_analysis_source.magerror, _cdb_analysis_source.deptherror, _cdb_analysis_source.horizontalerror, _cdb_analysis_source.rms, _cdb_analysis_source.dmin, _cdb_analysis_source.gap, _cdb_analysis_source.mag, _cdb_analysis_source.depth, _cdb_analysis_source.longitude, _cdb_analysis_source.latitude, _cdb_analysis_source.updated, _cdb_analysis_source.time;
Insert on analysis_b194a8f896_782928bfb14941a2d63f363041868eed044e3681 (cost=605.41..664.74 rows=210 width=262)
-> HashAggregate (cost=605.41..662.64 rows=210 width=262)
Group Key: analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.cartodb_id, analysis_7530d60ffc_fed067036aa3ef701ad62e03ab
b68b18e798ee3a.the_geom, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom_webmercator, analysis_7530d60ffc_fed067036aa
3ef701ad62e03abb68b18e798ee3a.magnst, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.nst, analysis_7530d60ffc_fed067036aa3ef7
01ad62e03abb68b18e798ee3a.magsource, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.locationsource, analysis_7530d60ffc_fed06
7036aa3ef701ad62e03abb68b18e798ee3a.status, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.type, analysis_7530d60ffc_fed06703
6aa3ef701ad62e03abb68b18e798ee3a.place, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.id, analysis_7530d60ffc_fed067036aa3ef
701ad62e03abb68b18e798ee3a.net, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.magtype, analysis_7530d60ffc_fed067036aa3ef701
ad62e03abb68b18e798ee3a.magerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.deptherror, analysis_7530d60ffc_fed067036aa3
ef701ad62e03abb68b18e798ee3a.horizontalerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.rms, analysis_7530d60ffc_fed0670
36aa3ef701ad62e03abb68b18e798ee3a.dmin, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.gap, analysis_7530d60ffc_fed067036aa3e
f701ad62e03abb68b18e798ee3a.mag, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.depth, analysis_7530d60ffc_fed067036aa3ef701a
d62e03abb68b18e798ee3a.longitude, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.latitude, analysis_7530d60ffc_fed067036aa3ef
701ad62e03abb68b18e798ee3a.updated, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a."time"
-> Nested Loop (cost=0.14..591.24 rows=210 width=250)
-> Seq Scan on analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a (cost=0.00..46.68 rows=1068 width=246)
-> Index Scan using punts_wifi_1_the_geom_idx on punts_wifi_1 (cost=0.14..0.50 rows=1 width=36)
Index Cond: (analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom && the_geom)
Filter: _st_intersects(analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom, the_geom)It'd changed to something like this:
CREATE TABLE analysis_b194a8f896_782928bfb14941a2d63f363041868eed044e3681 AS SELECT _cdb_analysis_source.cartodb_id, _cdb_analysis_source.the_geom, _cdb_analysis_source.the_geom_webmercator, _cdb_analysis_source.magnst, _cdb_analysis_source.nst, _cdb_analysis_source.magsource, _cdb_analysis_source.locationsource, _cdb_analysis_source.status, _cdb_analysis_source.type, _cdb_analysis_source.place, _cdb_analysis_source.id, _cdb_analysis_source.net, _cdb_analysis_source.magtype, _cdb_analysis_source.magerror, _cdb_analysis_source.deptherror, _cdb_analysis_source.horizontalerror, _cdb_analysis_source.rms, _cdb_analysis_source.dmin, _cdb_analysis_source.gap, _cdb_analysis_source.mag, _cdb_analysis_source.depth, _cdb_analysis_source.longitude, _cdb_analysis_source.latitude, _cdb_analysis_source.updated, _cdb_analysis_source.time, count(_cdb_analysis_target.cartodb_id) as count_vals, count(_cdb_analysis_target.cartodb_id) / GREATEST(0.0000026, ST_Area((ST_Transform(_cdb_analysis_source.the_geom, 4326))::geography)) as count_vals_density
FROM (select * from analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a) _cdb_analysis_source,
(SELECT * FROM punts_wifi_1) _cdb_analysis_target
WHERE ST_Intersects(_cdb_analysis_source.the_geom, _cdb_analysis_target.the_geom)
GROUP BY _cdb_analysis_source.cartodb_id, _cdb_analysis_source.the_geom, _cdb_analysis_source.the_geom_webmercator, _cdb_analysis_source.magnst, _cdb_analysis_source.nst, _cdb_analysis_source.magsource, _cdb_analysis_source.locationsource, _cdb_analysis_source.status, _cdb_analysis_source.type, _cdb_analysis_source.place, _cdb_analysis_source.id, _cdb_analysis_source.net, _cdb_analysis_source.magtype, _cdb_analysis_source.magerror, _cdb_analysis_source.deptherror, _cdb_analysis_source.horizontalerror, _cdb_analysis_source.rms, _cdb_analysis_source.dmin, _cdb_analysis_source.gap, _cdb_analysis_source.mag, _cdb_analysis_source.depth, _cdb_analysis_source.longitude, _cdb_analysis_source.latitude, _cdb_analysis_source.updated, _cdb_analysis_source.time;
Gather (cost=1605.41..1683.64 rows=210 width=262)
Workers Planned: 1
Single Copy: true
-> HashAggregate (cost=605.41..662.64 rows=210 width=262)
Group Key: analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.cartodb_id, analysis_7530d60ffc_fed067036aa3ef701ad62e03ab
b68b18e798ee3a.the_geom, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom_webmercator, analysis_7530d60ffc_fed067036aa
3ef701ad62e03abb68b18e798ee3a.magnst, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.nst, analysis_7530d60ffc_fed067036aa3ef7
01ad62e03abb68b18e798ee3a.magsource, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.locationsource, analysis_7530d60ffc_fed06
7036aa3ef701ad62e03abb68b18e798ee3a.status, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.type, analysis_7530d60ffc_fed06703
6aa3ef701ad62e03abb68b18e798ee3a.place, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.id, analysis_7530d60ffc_fed067036aa3ef
701ad62e03abb68b18e798ee3a.net, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.magtype, analysis_7530d60ffc_fed067036aa3ef701
ad62e03abb68b18e798ee3a.magerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.deptherror, analysis_7530d60ffc_fed067036aa3
ef701ad62e03abb68b18e798ee3a.horizontalerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.rms, analysis_7530d60ffc_fed0670
36aa3ef701ad62e03abb68b18e798ee3a.dmin, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.gap, analysis_7530d60ffc_fed067036aa3e
f701ad62e03abb68b18e798ee3a.mag, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.depth, analysis_7530d60ffc_fed067036aa3ef701a
d62e03abb68b18e798ee3a.longitude, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.latitude, analysis_7530d60ffc_fed067036aa3ef
701ad62e03abb68b18e798ee3a.updated, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a."time"
-> Nested Loop (cost=0.14..591.24 rows=210 width=250)
-> Seq Scan on analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a (cost=0.00..46.68 rows=1068 width=246)
-> Index Scan using punts_wifi_1_the_geom_idx on punts_wifi_1 (cost=0.14..0.50 rows=1 width=36)
Index Cond: (analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom && the_geom)
Filter: _st_intersects(analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom, the_geom)
(10 rows)- This has been applied in PostgreSQL master (current 11dev) and we need it in pg10 (branch REL_10_STABLE), so next I'll test if it can be applied cleanly.
from camshaft.
Algunenano
commented on July 18, 2024
Tested with REL_10_STABLE + e9baa5e9fa147e00a2466ab2c40eb99c8a700824 and it appears to work too:
cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# SELECT version();
version
-----------------------------------------------------------------------------
PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.0, 64-bit
(1 row)
cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# EXPLAIN CREATE TABLE analysis_b194a8f896_782928bfb14941a2d63f363041868eed044e3681 AS SELECT _cdb_analysis_source.cartodb_id, _cdb_analysis_source.the_geom, _cdb_analysis_source.the_geom_webmercator, _cdb_analysis_source.magnst, _cdb_analysis_source.nst, _cdb_analysis_source.magsource, _cdb_analysis_source.locationsource, _cdb_analysis_source.status, _cdb_analysis_source.type, _cdb_analysis_source.place, _cdb_analysis_source.id, _cdb_analysis_source.net, _cdb_analysis_source.magtype, _cdb_analysis_source.magerror, _cdb_analysis_source.deptherror, _cdb_analysis_source.horizontalerror, _cdb_analysis_source.rms, _cdb_analysis_source.dmin, _cdb_analysis_source.gap, _cdb_analysis_source.mag, _cdb_analysis_source.depth, _cdb_analysis_source.longitude, _cdb_analysis_source.latitude, _cdb_analysis_source.updated, _cdb_analysis_source.time, count(_cdb_analysis_target.cartodb_id) as count_vals, count(_cdb_analysis_target.cartodb_id) / GREATEST(0.0000026, ST_Area((ST_Transform(_cdb_analysis_source.the_geom, 4326))::geography)) as count_vals_density
FROM (select * from analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a) _cdb_analysis_source,
(SELECT * FROM punts_wifi_1) _cdb_analysis_target
WHERE ST_Intersects(_cdb_analysis_source.the_geom, _cdb_analysis_target.the_geom)
GROUP BY _cdb_analysis_source.cartodb_id, _cdb_analysis_source.the_geom, _cdb_analysis_source.the_geom_webmercator, _cdb_analysis_source.magnst, _cdb_analysis_source.nst, _cdb_analysis_source.magsource, _cdb_analysis_source.locationsource, _cdb_analysis_source.status, _cdb_analysis_source.type, _cdb_analysis_source.place, _cdb_analysis_source.id, _cdb_analysis_source.net, _cdb_analysis_source.magtype, _cdb_analysis_source.magerror, _cdb_analysis_source.deptherror, _cdb_analysis_source.horizontalerror, _cdb_analysis_source.rms, _cdb_analysis_source.dmin, _cdb_analysis_source.gap, _cdb_analysis_source.mag, _cdb_analysis_source.depth, _cdb_analysis_source.longitude, _cdb_analysis_source.latitude, _cdb_analysis_source.updated, _cdb_analysis_source.time;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
Gather (cost=1599.34..1692.26 rows=210 width=262)
Workers Planned: 1
Single Copy: true
-> GroupAggregate (cost=599.34..671.26 rows=210 width=262)
Group Key: analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.cartodb_id, analysis_7530d60ffc_fed067036aa3ef701ad62e03ab
b68b18e798ee3a.the_geom, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom_webmercator, analysis_7530d60ffc_fed067036aa
3ef701ad62e03abb68b18e798ee3a.magnst, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.nst, analysis_7530d60ffc_fed067036aa3ef7
01ad62e03abb68b18e798ee3a.magsource, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.locationsource, analysis_7530d60ffc_fed06
7036aa3ef701ad62e03abb68b18e798ee3a.status, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.type, analysis_7530d60ffc_fed06703
6aa3ef701ad62e03abb68b18e798ee3a.place, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.id, analysis_7530d60ffc_fed067036aa3ef
701ad62e03abb68b18e798ee3a.net, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.magtype, analysis_7530d60ffc_fed067036aa3ef701
ad62e03abb68b18e798ee3a.magerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.deptherror, analysis_7530d60ffc_fed067036aa3
ef701ad62e03abb68b18e798ee3a.horizontalerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.rms, analysis_7530d60ffc_fed0670
36aa3ef701ad62e03abb68b18e798ee3a.dmin, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.gap, analysis_7530d60ffc_fed067036aa3e
f701ad62e03abb68b18e798ee3a.mag, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.depth, analysis_7530d60ffc_fed067036aa3ef701a
d62e03abb68b18e798ee3a.longitude, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.latitude, analysis_7530d60ffc_fed067036aa3ef
701ad62e03abb68b18e798ee3a.updated, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a."time"
-> Sort (cost=599.34..599.86 rows=210 width=250)
Sort Key: analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.cartodb_id, analysis_7530d60ffc_fed067036aa3ef701ad62
e03abb68b18e798ee3a.the_geom, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom_webmercator, analysis_7530d60ffc_fed067
036aa3ef701ad62e03abb68b18e798ee3a.magnst, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.nst, analysis_7530d60ffc_fed067036a
a3ef701ad62e03abb68b18e798ee3a.magsource, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.locationsource, analysis_7530d60ffc_
fed067036aa3ef701ad62e03abb68b18e798ee3a.status, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.type, analysis_7530d60ffc_fed
067036aa3ef701ad62e03abb68b18e798ee3a.place, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.id, analysis_7530d60ffc_fed067036
aa3ef701ad62e03abb68b18e798ee3a.net, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.magtype, analysis_7530d60ffc_fed067036aa3
ef701ad62e03abb68b18e798ee3a.magerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.deptherror, analysis_7530d60ffc_fed0670
36aa3ef701ad62e03abb68b18e798ee3a.horizontalerror, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.rms, analysis_7530d60ffc_fe
d067036aa3ef701ad62e03abb68b18e798ee3a.dmin, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.gap, analysis_7530d60ffc_fed06703
6aa3ef701ad62e03abb68b18e798ee3a.mag, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.depth, analysis_7530d60ffc_fed067036aa3e
f701ad62e03abb68b18e798ee3a.longitude, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.latitude, analysis_7530d60ffc_fed067036
aa3ef701ad62e03abb68b18e798ee3a.updated, analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a."time"
-> Nested Loop (cost=0.14..591.24 rows=210 width=250)
-> Seq Scan on analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a (cost=0.00..46.68 rows=1068 width=246)
-> Index Scan using punts_wifi_1_the_geom_idx on punts_wifi_1 (cost=0.14..0.50 rows=1 width=36)
Index Cond: (analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom && the_geom)
Filter: _st_intersects(analysis_7530d60ffc_fed067036aa3ef701ad62e03abb68b18e798ee3a.the_geom, the_geom)
(12 rows)from camshaft.
Algunenano
commented on July 18, 2024
Small update about current status of the issue: I see several things need to be done to have good analysis parallelization with PG10:
- Review and merge pending PRs (CartoDB/crankshaft#183, CartoDB/crankshaft#183).
- Update our postgresql REL_10_STABLE branch to current postgresql/REL_10_STABLE (10.1 I think) and apply the patch to be able to use CREATE TABLE AS with several workers. Also apply any other pending patch (I can think about at least 2: stoppable plpython and the one to improve how function cost impacts the planner)
- Modify how camshaft creates cache tables so, instead of first CREATE'ing and then INSERT'ing, directly create it with CREATE TABLE xxxx AS
long parallelizable query. - As marked in this document (still WIP), some of the analysis aren't parallelizable yet. They need to be reviewed and modified.
- Of all the parallelizable analysis I've tested, none of them have use a parallel plan it so we'd need to test more and tune function cost to help the planner.
from camshaft.
Algunenano
commented on July 18, 2024
Stale
from camshaft.
Related Issues (20)
- DO: Error augmenting with text columns HOT 1
- Allow executing arbitrary SQL for analysis preparation HOT 7
- Performance research: run independent analyses in parallel HOT 1
- Prevent column name collisions in node queries HOT 2
- Depricated SQL nodes not working HOT 18
- Invalid geometries generated with Area of Intereset HOT 1
- DO analysis fails with multiple columns with the same numerator HOT 2
- Poor SQL for nearest neighbor join
- Force "merge" analysis to be cached HOT 2
- Map instantiation: Get columns from cache table instead of using `LIMIT 0` when avaiblable HOT 9
- Add .npmignore HOT 1
- IMHO Misleading `limits_error` message
- Uncaught exception in some Batch API errors HOT 1
- Polygons from geometries analysis should be cached HOT 1
- Uncaught exception: Error [ERR_STREAM_DESTROYED]: Cannot call write after a stream was destroyed HOT 17
- Error with reserved words
- Replace CDB_QueryTables_Updated_At with cartodb-query-tables HOT 1
- Noise in the analysis logs HOT 4
- Uncaught exception: Range filter expect to have at least one value in greater_than, greater_than_or_equal, less_than, less_than_or_equal, min, or max numeric params
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from camshaft.