Index scan is not happening on tables in data nodes in multi node architecture

1

I have a 2 server Database setup with timescale installed, one server has 1 access node and another server has 2 data nodes.

I have a query that joins tables in the access node and data node and I am trying to fetch a few million rows over a few days range.
The query takes 20 minutes to run and I observed Custom Scan/Data Node scan is happening instead of Index Scan.
We ingest more than a million rows every 5 minutes (40GB per day)

Example:
→ Custom Scan (AsyncAppend) (cost=101.33…35936027.84 rows=29464 width=24) (actual time=428.564…5473.172 rows=7254167 loops=1)

I would like to know if this is a limitation of Timescale DB or I am missing something.

1 Like
2

Hi Sri! Welcome to our Forum!

Can you please try running
set timescaledb.enable_remote_explain = true

Then running the explain with EXPLAIN (verbose)

You should be able to see information about the plan it is using on each datanode.

3 
Sort  (cost=33646550.32..33651776.11 rows=2090319 width=95)
"  Sort Key: ((r.""time"")::timestamp without time zone)"
  ->  Hash Join  (cost=759.23..33364345.03 rows=2090319 width=95)
        Hash Cond: (n.node_type_id = nt.id)
        ->  Hash Join  (cost=757.85..33347038.18 rows=2090319 width=67)
              Hash Cond: (r.node_id = n.id)
              ->  Hash Join  (cost=707.94..33341481.74 rows=2090319 width=37)
                    Hash Cond: (r.index_id = i.id)
                    ->  Custom Scan (AsyncAppend)  (cost=101.33..33325403.72 rows=5892892 width=24)
                          ->  Append  (cost=101.33..33325403.72 rows=5892892 width=24)
                                ->  Custom Scan (DataNodeScan) on kpi_results_kpiresult r_1  (cost=101.33..16647969.63 rows=2946446 width=24)
                                ->  Custom Scan (DataNodeScan) on kpi_results_kpiresult r_2  (cost=101.33..16647969.63 rows=2946446 width=24)
                    ->  Hash  (cost=493.34..493.34 rows=9062 width=21)
                          ->  Seq Scan on kpi_results_kpiindex i  (cost=0.00..493.34 rows=9062 width=21)
                                Filter: ((index)::text !~~ '%tunnel%'::text)
              ->  Hash  (cost=34.96..34.96 rows=1196 width=38)
                    ->  Seq Scan on node_node n  (cost=0.00..34.96 rows=1196 width=38)
        ->  Hash  (cost=1.17..1.17 rows=17 width=4)
              ->  Seq Scan on node_nodetype nt  (cost=0.00..1.17 rows=17 width=4)

I see this after running the command

4

@srnadend The CustomScan is only an execution node on the access node. The actual query plan on the data node is not visible unless you enable remote explains, as explained above.

You should also run your EXPLAIN with VERBOSE. Then you will be able to see the remote SQL statement sent to the data node. If the statement/query contains an ORDER BY clause, it is very likely that an index scan is done on the data node, assuming an index exists that matches the ordering keys.

TimescaleDB does not yet support remote (push-down) JOINs on data nodes, so they will be quite slow if you fetch a lot of data. Push-down JOINs is being worked on so it will be available in an upcoming release.

3 Likes
5

After adding EXPLAIN VERBOSE to SQL query:

Sort  (cost=33646550.32..33651776.11 rows=2090319 width=95)
"  Output: ((r.""time"")::timestamp without time zone), n.node_name, (split_part((n.node_name)::text, '.'::text, 1)), r.value, i.index"
"  Sort Key: ((r.""time"")::timestamp without time zone)"
  ->  Hash Join  (cost=759.23..33364345.03 rows=2090319 width=95)
"        Output: (r.""time"")::timestamp without time zone, n.node_name, split_part((n.node_name)::text, '.'::text, 1), r.value, i.index"
        Inner Unique: true
        Hash Cond: (n.node_type_id = nt.id)
        ->  Hash Join  (cost=757.85..33347038.18 rows=2090319 width=67)
"              Output: r.""time"", r.value, n.node_name, n.node_type_id, i.index"
              Inner Unique: true
              Hash Cond: (r.node_id = n.id)
              ->  Hash Join  (cost=707.94..33341481.74 rows=2090319 width=37)
"                    Output: r.""time"", r.value, r.node_id, i.index"
                    Inner Unique: true
                    Hash Cond: (r.index_id = i.id)
                    ->  Custom Scan (AsyncAppend)  (cost=101.33..33325403.72 rows=5892892 width=24)
"                          Output: r.""time"", r.value, r.node_id, r.index_id"
                          ->  Append  (cost=101.33..33325403.72 rows=5892892 width=24)
                                ->  Custom Scan (DataNodeScan) on public.kpi_results_kpiresult r_1  (cost=101.33..16647969.63 rows=2946446 width=24)
"                                      Output: r_1.""time"", r_1.value, r_1.node_id, r_1.index_id"
                                      Data node: dataNode1
                                      Chunks: _dist_hyper_1_3_chunk
"                                      Remote SQL: SELECT ""time"", value, index_id, node_id FROM public.kpi_results_kpiresult WHERE _timescaledb_internal.chunks_in(public.kpi_results_kpiresult.*, ARRAY[3]) AND ((""time"" >= '2023-02-10 00:00:00+00'::timestamp with time zone)) AND ((""time"" <= '2023-02-16 00:00:00+00'::timestamp with time zone)) AND ((node_id = ANY ('{807,1243,1174,1173,3998,1278,1043,910,3645,2915,3615,1138,543,646,990,539,654,503,1088,1091,594,609,1166,1040,498,4390,3616,817,2830,847,662,905,652,3650,4634,3244,2885,3654,728,959,1289,4137,472,1068,2811,873,3617,818,1087,961,4494,4569,3647,1318,4040,597,900,672,977,985,808,766,1259,1012,512,2926,759,511,955,777,912,531,1187,4109,622,2916,1236,870,823,1263,1250,782,1085,787,3635,635,4112,526,828,1245,2832,785,497,1234,2813,1185,1231,474,4206,1042,970,2913,624,4314,991,538,492,1306,834,2625,2757,789,2919,976,4121,1124,736,1104,495,4329,3655,569,1079,878,3652,580,1313,642,1315,562,729,685,4239,628,564,742,1010,2778,1049,1005,1038,4120,750,591,4270,833,1140,795,681,706,4388,4108,502,951,613,658,3646,3240,617,1227,2933,3633,1293,1109,4268,1086,1070,4199,726,2826,812,523,514,4111,4254,650,4363,926,849,901,4735,824,1214,4470,1007,1230,3614,462,973,3610,1300,4114,1201,1205,509,881,1280,4046,1067,3643,4110,4163,962,767,1084,1208,734,4333,4632,4292,3630,1288,1229,4620,798,772,1150,1098,4386,978,816,2736,716,2856,1149,1018,701,4300,4113,496,3624,4552,957,4117,3242,1083,1052,467,1248,3640,3651,565,1282,3622,947,4115,993,3603,587,1080,1304,942,2848,3636,1066,972,3632,2837,3634,670,1316,837,3642,875,744,916,733,572,1184,517,468,2869,815,893,548,4412,684,2920,4352,3620,857,835,1101,532,820,831,1145,4267,3641,919,3122,727,4119,3621,3228,4249,1054,1290,1069,469,2816,1175,1160,2917,1108,708,1142,508,3628,885,1050,4123,4395,1303,4400,762,4565,735,2805,1016,1165,731,2840,478,2807,4617,647,529,535,590,602,3123,4421,3638,4575,868,4105,4207,1204,473,4402,4566,749,546,753,522,3627,718,4106,889,4118,3639,886,3815,582,2755,4459,588,4377,4385,4590,598,656,506,4122,601,4341,2819,3235,1180,4733,4104,4102,649,3631,3619,3623,1256,1269,1281,1232,2776,1298,516,2770,500,623,741,1133,595,822,936,3981,504,3626,909,4269,3618,3613,1255,768,3611,566,4208,1023,3189,1317,800,2886,4464,4704,669,3648,779,524,4531,3629,809,1141,894,3649,2850,2844,859,997,1215,1198,1267,4389,852,612,510,608,992,1056,4714,2914,520,3625,888,1264,4107,872,3814,1033,3606,797,2929,534,746,4529,1188,1011,665,1237,4162,3609,1291,499,4116,545,821,673,585,703,1254,846,4504,880,981,1144,2918,3236,3231,486,915,792,4238,4246,2889,3237,4327,1130,4726,475,861,4410,488,1024,874,1053,4720,515,979,836,3972,1060,491,554,501,1131,513,4234,3241,3234,3239,897,464,2891,536,3644,690,1206,1207,4567,463,740,995,471,3653,3612,946,1128,700,4247,937,4532,3267,4497,3637,913}'::integer[])))"
                                      Remote EXPLAIN: 
                                        Index Scan using _dist_hyper_1_3_chunk_kpi_results_kpiresult_node_id_time_idx on _timescaledb_internal._dist_hyper_1_3_chunk  (cost=0.57..2984279.70 rows=53131439 width=24)
"                                          Output: _dist_hyper_1_3_chunk.""time"", _dist_hyper_1_3_chunk.value, _dist_hyper_1_3_chunk.index_id, _dist_hyper_1_3_chunk.node_id"
"                                          Index Cond: ((_dist_hyper_1_3_chunk.node_id = ANY ('{807,1243,1174,1173,3998,1278,1043,910,3645,2915,3615,1138,543,646,990,539,654,503,1088,1091,594,609,1166,1040,498,4390,3616,817,2830,847,662,905,652,3650,4634,3244,2885,3654,728,959,1289,4137,472,1068,2811,873,3617,818,1087,961,4494,4569,3647,1318,4040,597,900,672,977,985,808,766,1259,1012,512,2926,759,511,955,777,912,531,1187,4109,622,2916,1236,870,823,1263,1250,782,1085,787,3635,635,4112,526,828,1245,2832,785,497,1234,2813,1185,1231,474,4206,1042,970,2913,624,4314,991,538,492,1306,834,2625,2757,789,2919,976,4121,1124,736,1104,495,4329,3655,569,1079,878,3652,580,1313,642,1315,562,729,685,4239,628,564,742,1010,2778,1049,1005,1038,4120,750,591,4270,833,1140,795,681,706,4388,4108,502,951,613,658,3646,3240,617,1227,2933,3633,1293,1109,4268,1086,1070,4199,726,2826,812,523,514,4111,4254,650,4363,926,849,901,4735,824,1214,4470,1007,1230,3614,462,973,3610,1300,4114,1201,1205,509,881,1280,4046,1067,3643,4110,4163,962,767,1084,1208,734,4333,4632,4292,3630,1288,1229,4620,798,772,1150,1098,4386,978,816,2736,716,2856,1149,1018,701,4300,4113,496,3624,4552,957,4117,3242,1083,1052,467,1248,3640,3651,565,1282,3622,947,4115,993,3603,587,1080,1304,942,2848,3636,1066,972,3632,2837,3634,670,1316,837,3642,875,744,916,733,572,1184,517,468,2869,815,893,548,4412,684,2920,4352,3620,857,835,1101,532,820,831,1145,4267,3641,919,3122,727,4119,3621,3228,4249,1054,1290,1069,469,2816,1175,1160,2917,1108,708,1142,508,3628,885,1050,4123,4395,1303,4400,762,4565,735,2805,1016,1165,731,2840,478,2807,4617,647,529,535,590,602,3123,4421,3638,4575,868,4105,4207,1204,473,4402,4566,749,546,753,522,3627,718,4106,889,4118,3639,886,3815,582,2755,4459,588,4377,4385,4590,598,656,506,4122,601,4341,2819,3235,1180,4733,4104,4102,649,3631,3619,3623,1256,1269,1281,1232,2776,1298,516,2770,500,623,741,1133,595,822,936,3981,504,3626,909,4269,3618,3613,1255,768,3611,566,4208,1023,3189,1317,800,2886,4464,4704,669,3648,779,524,4531,3629,809,1141,894,3649,2850,2844,859,997,1215,1198,1267,4389,852,612,510,608,992,1056,4714,2914,520,3625,888,1264,4107,872,3814,1033,3606,797,2929,534,746,4529,1188,1011,665,1237,4162,3609,1291,499,4116,545,821,673,585,703,1254,846,4504,880,981,1144,2918,3236,3231,486,915,792,4238,4246,2889,3237,4327,1130,4726,475,861,4410,488,1024,874,1053,4720,515,979,836,3972,1060,491,554,501,1131,513,4234,3241,3234,3239,897,464,2891,536,3644,690,1206,1207,4567,463,740,995,471,3653,3612,946,1128,700,4247,937,4532,3267,4497,3637,913}'::integer[])) AND (_dist_hyper_1_3_chunk.""time"" >= '2023-02-10 00:00:00+00'::timestamp with time zone) AND (_dist_hyper_1_3_chunk.""time"" <= '2023-02-16 00:00:00+00'::timestamp with time zone))"
                                        JIT:
                                          Functions: 4
                                          Options: Inlining true, Optimization true, Expressions true, Deforming true
""
                                ->  Custom Scan (DataNodeScan) on public.kpi_results_kpiresult r_2  (cost=101.33..16647969.63 rows=2946446 width=24)
"                                      Output: r_2.""time"", r_2.value, r_2.node_id, r_2.index_id"
                                      Data node: dataNode2
                                      Chunks: _dist_hyper_1_4_chunk
"                                      Remote SQL: SELECT ""time"", value, index_id, node_id FROM public.kpi_results_kpiresult WHERE _timescaledb_internal.chunks_in(public.kpi_results_kpiresult.*, ARRAY[1]) AND ((""time"" >= '2023-02-10 00:00:00+00'::timestamp with time zone)) AND ((""time"" <= '2023-02-16 00:00:00+00'::timestamp with time zone)) AND ((node_id = ANY ('{807,1243,1174,1173,3998,1278,1043,910,3645,2915,3615,1138,543,646,990,539,654,503,1088,1091,594,609,1166,1040,498,4390,3616,817,2830,847,662,905,652,3650,4634,3244,2885,3654,728,959,1289,4137,472,1068,2811,873,3617,818,1087,961,4494,4569,3647,1318,4040,597,900,672,977,985,808,766,1259,1012,512,2926,759,511,955,777,912,531,1187,4109,622,2916,1236,870,823,1263,1250,782,1085,787,3635,635,4112,526,828,1245,2832,785,497,1234,2813,1185,1231,474,4206,1042,970,2913,624,4314,991,538,492,1306,834,2625,2757,789,2919,976,4121,1124,736,1104,495,4329,3655,569,1079,878,3652,580,1313,642,1315,562,729,685,4239,628,564,742,1010,2778,1049,1005,1038,4120,750,591,4270,833,1140,795,681,706,4388,4108,502,951,613,658,3646,3240,617,1227,2933,3633,1293,1109,4268,1086,1070,4199,726,2826,812,523,514,4111,4254,650,4363,926,849,901,4735,824,1214,4470,1007,1230,3614,462,973,3610,1300,4114,1201,1205,509,881,1280,4046,1067,3643,4110,4163,962,767,1084,1208,734,4333,4632,4292,3630,1288,1229,4620,798,772,1150,1098,4386,978,816,2736,716,2856,1149,1018,701,4300,4113,496,3624,4552,957,4117,3242,1083,1052,467,1248,3640,3651,565,1282,3622,947,4115,993,3603,587,1080,1304,942,2848,3636,1066,972,3632,2837,3634,670,1316,837,3642,875,744,916,733,572,1184,517,468,2869,815,893,548,4412,684,2920,4352,3620,857,835,1101,532,820,831,1145,4267,3641,919,3122,727,4119,3621,3228,4249,1054,1290,1069,469,2816,1175,1160,2917,1108,708,1142,508,3628,885,1050,4123,4395,1303,4400,762,4565,735,2805,1016,1165,731,2840,478,2807,4617,647,529,535,590,602,3123,4421,3638,4575,868,4105,4207,1204,473,4402,4566,749,546,753,522,3627,718,4106,889,4118,3639,886,3815,582,2755,4459,588,4377,4385,4590,598,656,506,4122,601,4341,2819,3235,1180,4733,4104,4102,649,3631,3619,3623,1256,1269,1281,1232,2776,1298,516,2770,500,623,741,1133,595,822,936,3981,504,3626,909,4269,3618,3613,1255,768,3611,566,4208,1023,3189,1317,800,2886,4464,4704,669,3648,779,524,4531,3629,809,1141,894,3649,2850,2844,859,997,1215,1198,1267,4389,852,612,510,608,992,1056,4714,2914,520,3625,888,1264,4107,872,3814,1033,3606,797,2929,534,746,4529,1188,1011,665,1237,4162,3609,1291,499,4116,545,821,673,585,703,1254,846,4504,880,981,1144,2918,3236,3231,486,915,792,4238,4246,2889,3237,4327,1130,4726,475,861,4410,488,1024,874,1053,4720,515,979,836,3972,1060,491,554,501,1131,513,4234,3241,3234,3239,897,464,2891,536,3644,690,1206,1207,4567,463,740,995,471,3653,3612,946,1128,700,4247,937,4532,3267,4497,3637,913}'::integer[])))"
                                      Remote EXPLAIN: 
                                        Index Scan using _dist_hyper_1_4_chunk_kpi_results_kpiresult_node_id_time_idx on _timescaledb_internal._dist_hyper_1_4_chunk  (cost=0.57..2944866.05 rows=51624917 width=24)
"                                          Output: _dist_hyper_1_4_chunk.""time"", _dist_hyper_1_4_chunk.value, _dist_hyper_1_4_chunk.index_id, _dist_hyper_1_4_chunk.node_id"
"                                          Index Cond: ((_dist_hyper_1_4_chunk.node_id = ANY ('{807,1243,1174,1173,3998,1278,1043,910,3645,2915,3615,1138,543,646,990,539,654,503,1088,1091,594,609,1166,1040,498,4390,3616,817,2830,847,662,905,652,3650,4634,3244,2885,3654,728,959,1289,4137,472,1068,2811,873,3617,818,1087,961,4494,4569,3647,1318,4040,597,900,672,977,985,808,766,1259,1012,512,2926,759,511,955,777,912,531,1187,4109,622,2916,1236,870,823,1263,1250,782,1085,787,3635,635,4112,526,828,1245,2832,785,497,1234,2813,1185,1231,474,4206,1042,970,2913,624,4314,991,538,492,1306,834,2625,2757,789,2919,976,4121,1124,736,1104,495,4329,3655,569,1079,878,3652,580,1313,642,1315,562,729,685,4239,628,564,742,1010,2778,1049,1005,1038,4120,750,591,4270,833,1140,795,681,706,4388,4108,502,951,613,658,3646,3240,617,1227,2933,3633,1293,1109,4268,1086,1070,4199,726,2826,812,523,514,4111,4254,650,4363,926,849,901,4735,824,1214,4470,1007,1230,3614,462,973,3610,1300,4114,1201,1205,509,881,1280,4046,1067,3643,4110,4163,962,767,1084,1208,734,4333,4632,4292,3630,1288,1229,4620,798,772,1150,1098,4386,978,816,2736,716,2856,1149,1018,701,4300,4113,496,3624,4552,957,4117,3242,1083,1052,467,1248,3640,3651,565,1282,3622,947,4115,993,3603,587,1080,1304,942,2848,3636,1066,972,3632,2837,3634,670,1316,837,3642,875,744,916,733,572,1184,517,468,2869,815,893,548,4412,684,2920,4352,3620,857,835,1101,532,820,831,1145,4267,3641,919,3122,727,4119,3621,3228,4249,1054,1290,1069,469,2816,1175,1160,2917,1108,708,1142,508,3628,885,1050,4123,4395,1303,4400,762,4565,735,2805,1016,1165,731,2840,478,2807,4617,647,529,535,590,602,3123,4421,3638,4575,868,4105,4207,1204,473,4402,4566,749,546,753,522,3627,718,4106,889,4118,3639,886,3815,582,2755,4459,588,4377,4385,4590,598,656,506,4122,601,4341,2819,3235,1180,4733,4104,4102,649,3631,3619,3623,1256,1269,1281,1232,2776,1298,516,2770,500,623,741,1133,595,822,936,3981,504,3626,909,4269,3618,3613,1255,768,3611,566,4208,1023,3189,1317,800,2886,4464,4704,669,3648,779,524,4531,3629,809,1141,894,3649,2850,2844,859,997,1215,1198,1267,4389,852,612,510,608,992,1056,4714,2914,520,3625,888,1264,4107,872,3814,1033,3606,797,2929,534,746,4529,1188,1011,665,1237,4162,3609,1291,499,4116,545,821,673,585,703,1254,846,4504,880,981,1144,2918,3236,3231,486,915,792,4238,4246,2889,3237,4327,1130,4726,475,861,4410,488,1024,874,1053,4720,515,979,836,3972,1060,491,554,501,1131,513,4234,3241,3234,3239,897,464,2891,536,3644,690,1206,1207,4567,463,740,995,471,3653,3612,946,1128,700,4247,937,4532,3267,4497,3637,913}'::integer[])) AND (_dist_hyper_1_4_chunk.""time"" >= '2023-02-10 00:00:00+00'::timestamp with time zone) AND (_dist_hyper_1_4_chunk.""time"" <= '2023-02-16 00:00:00+00'::timestamp with time zone))"
                                        JIT:
                                          Functions: 4
                                          Options: Inlining true, Optimization true, Expressions true, Deforming true
""
                    ->  Hash  (cost=493.34..493.34 rows=9062 width=21)
                          Output: i.index, i.id
                          ->  Seq Scan on public.kpi_results_kpiindex i  (cost=0.00..493.34 rows=9062 width=21)
                                Output: i.index, i.id
                                Filter: ((i.index)::text !~~ '%tunnel%'::text)
              ->  Hash  (cost=34.96..34.96 rows=1196 width=38)
                    Output: n.node_name, n.id, n.node_type_id
                    ->  Seq Scan on public.node_node n  (cost=0.00..34.96 rows=1196 width=38)
                          Output: n.node_name, n.id, n.node_type_id
        ->  Hash  (cost=1.17..1.17 rows=17 width=4)
              Output: nt.id
              ->  Seq Scan on public.node_nodetype nt  (cost=0.00..1.17 rows=17 width=4)
                    Output: nt.id
6

Hi,

As can be seen below the Datanode is using an INDEX scan.

as Eric mentioned, push-down JOINs is being worked on so it will be available in an upcoming release.

1 Like
7

Thanks Nikhil!

I see it is using IndexScan as well on the data nodes.

What are the push-down JOINs and how does it help in retrieving the data faster ?

One more test I did is: I moved the data from distributed hyper tables on data nodes into a single node setup with hyper table and this query is faster comparably.

Any thoughts on how to improve the performance of retrieving lots of data?

1 Like
8

Also i would like to know, when is the upcoming release ?
Does the next release has Push Down Joins feature ?