데이터베이스 질문과 답변

Select query문 속도관련하여 질문드립니다!! 0 1 1,709

by Harzarics [SQL Query] postgres tsdb optimizing [2022.06.24 15:21:28]

안녕하세요.

sql query : SELECT value from tsdb.vibration WHERE time >'2021-12-24 14:00:00' and sensor_id ='102' and gateway_channel_id  = '305'

위와 같은 query를 더 빠른 속도로 조회를 하고 싶습니다.

postgtes db를 사용하고 아래와 같은 실행계획으로 처리됩니다.

Where조건의 컬럼들은 전부 index가 존재합니다.

실제 db tool에서 15분정도 걸리는데 이 정도보다 저 줄이고 싶은데 방법이 있을까 여쭤봅니다.

감사합니다.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Append  (cost=17067.84..347344.40 rows=97064 width=4)
  ->  Bitmap Heap Scan on _hyper_2_393_chunk  (cost=17067.84..24848.68 rows=7032 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=17067.84..17067.84 rows=7032 width=0)
              ->  Bitmap Index Scan on _hyper_2_393_chunk_vibration_gateway_channel_id_idx  (cost=0.00..4287.94 rows=378490 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_393_chunk_vibration_sensor_id_idx  (cost=0.00..12776.14 rows=1128183 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_285_chunk  (cost=9003.34..13024.66 rows=3633 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=9003.34..9003.34 rows=3633 width=0)
              ->  Bitmap Index Scan on _hyper_2_285_chunk_vibration_gateway_channel_id_idx  (cost=0.00..2242.97 rows=198081 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_285_chunk_vibration_sensor_id_idx  (cost=0.00..6758.30 rows=596845 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_375_chunk  (cost=15542.71..21547.94 rows=5416 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=15542.71..15542.71 rows=5416 width=0)
              ->  Bitmap Index Scan on _hyper_2_375_chunk_vibration_gateway_channel_id_idx  (cost=0.00..4037.19 rows=356497 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_375_chunk_vibration_sensor_id_idx  (cost=0.00..11502.56 rows=1015572 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Index Scan using _hyper_2_359_chunk_vibration_gateway_channel_id_idx on _hyper_2_359_chunk  (cost=0.56..2.79 rows=1 width=4)
        Index Cond: (gateway_channel_id = 305)
        Filter: (("time" > '2021-12-24 14:00:00+09'::timestamp with time zone) AND (sensor_id = 102))
  ->  Bitmap Heap Scan on _hyper_2_280_chunk  (cost=8376.63..11918.64 rows=2607 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=8376.63..8376.63 rows=3198 width=0)
              ->  Bitmap Index Scan on _hyper_2_280_chunk_vibration_gateway_channel_id_idx  (cost=0.00..2092.57 rows=184628 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_280_chunk_vibration_sensor_id_idx  (cost=0.00..6282.50 rows=554525 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_295_chunk  (cost=6486.40..8675.59 rows=1972 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=6486.40..6486.40 rows=1972 width=0)
              ->  Bitmap Index Scan on _hyper_2_295_chunk_vibration_gateway_channel_id_idx  (cost=0.00..1649.58 rows=145509 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_295_chunk_vibration_sensor_id_idx  (cost=0.00..4835.59 rows=426870 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_303_chunk  (cost=9279.97..13432.13 rows=3751 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=9279.97..9279.97 rows=3751 width=0)
              ->  Bitmap Index Scan on _hyper_2_303_chunk_vibration_gateway_channel_id_idx  (cost=0.00..2296.78 rows=202762 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_303_chunk_vibration_sensor_id_idx  (cost=0.00..6981.06 rows=616280 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_410_chunk  (cost=20122.60..32700.00 rows=11424 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=20122.60..20122.60 rows=11424 width=0)
              ->  Bitmap Index Scan on _hyper_2_410_chunk_vibration_gateway_channel_id_idx  (cost=0.00..5254.09 rows=463750 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_410_chunk_vibration_sensor_id_idx  (cost=0.00..14862.55 rows=1312065 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_384_chunk  (cost=16575.14..23349.60 rows=6114 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=16575.14..16575.14 rows=6114 width=0)
              ->  Bitmap Index Scan on _hyper_2_384_chunk_vibration_gateway_channel_id_idx  (cost=0.00..4100.96 rows=362066 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_384_chunk_vibration_sensor_id_idx  (cost=0.00..12470.88 rows=1100975 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_290_chunk  (cost=8737.68..12512.32 rows=3409 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=8737.68..8737.68 rows=3409 width=0)
              ->  Bitmap Index Scan on _hyper_2_290_chunk_vibration_gateway_channel_id_idx  (cost=0.00..2176.99 rows=192070 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_290_chunk_vibration_sensor_id_idx  (cost=0.00..6558.74 rows=579037 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_300_chunk  (cost=6659.85..9375.11 rows=2451 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=6659.85..6659.85 rows=2451 width=0)
              ->  Bitmap Index Scan on _hyper_2_300_chunk_vibration_gateway_channel_id_idx  (cost=0.00..1614.14 rows=142414 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_300_chunk_vibration_sensor_id_idx  (cost=0.00..5044.23 rows=445173 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_399_chunk  (cost=18258.30..27053.78 rows=7954 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=18258.30..18258.30 rows=7954 width=0)
              ->  Bitmap Index Scan on _hyper_2_399_chunk_vibration_gateway_channel_id_idx  (cost=0.00..4681.98 rows=413282 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_399_chunk_vibration_sensor_id_idx  (cost=0.00..13572.09 rows=1197937 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_404_chunk  (cost=18735.19..27419.61 rows=7852 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=18735.19..18735.19 rows=7852 width=0)
              ->  Bitmap Index Scan on _hyper_2_404_chunk_vibration_gateway_channel_id_idx  (cost=0.00..4484.33 rows=395875 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_404_chunk_vibration_sensor_id_idx  (cost=0.00..14246.69 rows=1258256 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Index Scan using _hyper_2_365_chunk_vibration_sensor_id_idx on _hyper_2_365_chunk  (cost=0.56..2.79 rows=1 width=4)
        Index Cond: (sensor_id = 102)
        Filter: (("time" > '2021-12-24 14:00:00+09'::timestamp with time zone) AND (gateway_channel_id = 305))
  ->  Bitmap Heap Scan on _hyper_2_315_chunk  (cost=8247.79..11508.02 rows=2941 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=8247.79..8247.79 rows=2941 width=0)
              ->  Bitmap Index Scan on _hyper_2_315_chunk_vibration_gateway_channel_id_idx  (cost=0.00..2054.96 rows=181373 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_315_chunk_vibration_sensor_id_idx  (cost=0.00..6191.10 rows=546592 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_345_chunk  (cost=181.92..183.04 rows=1 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=181.92..181.92 rows=1 width=0)
              ->  Bitmap Index Scan on _hyper_2_345_chunk_vibration_gateway_channel_id_idx  (cost=0.00..23.13 rows=1982 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_345_chunk_vibration_sensor_id_idx  (cost=0.00..158.54 rows=13877 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Index Scan using _hyper_2_355_chunk_vibration_sensor_id_idx on _hyper_2_355_chunk  (cost=0.56..2.79 rows=1 width=4)
        Index Cond: (sensor_id = 102)
        Filter: (("time" > '2021-12-24 14:00:00+09'::timestamp with time zone) AND (gateway_channel_id = 305))
  ->  Bitmap Heap Scan on _hyper_2_308_chunk  (cost=8891.05..12761.18 rows=3495 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=8891.05..8891.05 rows=3495 width=0)
              ->  Bitmap Index Scan on _hyper_2_308_chunk_vibration_gateway_channel_id_idx  (cost=0.00..2270.19 rows=200390 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_308_chunk_vibration_sensor_id_idx  (cost=0.00..6618.87 rows=584414 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_320_chunk  (cost=6916.75..9497.17 rows=2326 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=6916.75..6916.75 rows=2326 width=0)
              ->  Bitmap Index Scan on _hyper_2_320_chunk_vibration_gateway_channel_id_idx  (cost=0.00..1814.69 rows=160190 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_320_chunk_vibration_sensor_id_idx  (cost=0.00..5100.65 rows=450185 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_420_chunk  (cost=1762.09..2830.55 rows=970 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=1762.09..1762.09 rows=970 width=0)
              ->  Bitmap Index Scan on _hyper_2_420_chunk_vibration_gateway_channel_id_idx  (cost=0.00..439.00 rows=38676 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_420_chunk_vibration_sensor_id_idx  (cost=0.00..1322.35 rows=116709 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_339_chunk  (cost=19298.28..28061.11 rows=7919 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=19298.28..19298.28 rows=7919 width=0)
              ->  Bitmap Index Scan on _hyper_2_339_chunk_vibration_gateway_channel_id_idx  (cost=0.00..4748.10 rows=419164 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_339_chunk_vibration_sensor_id_idx  (cost=0.00..14545.98 rows=1284228 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_334_chunk  (cost=4134.44..4611.90 rows=428 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=4134.44..4134.44 rows=428 width=0)
              ->  Bitmap Index Scan on _hyper_2_334_chunk_vibration_gateway_channel_id_idx  (cost=0.00..1054.23 rows=92968 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_334_chunk_vibration_sensor_id_idx  (cost=0.00..3079.76 rows=271812 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Index Scan using _hyper_2_350_chunk_vibration_gateway_channel_id_idx on _hyper_2_350_chunk  (cost=0.56..2.79 rows=1 width=4)
        Index Cond: (gateway_channel_id = 305)
        Filter: (("time" > '2021-12-24 14:00:00+09'::timestamp with time zone) AND (sensor_id = 102))
  ->  Bitmap Heap Scan on _hyper_2_370_chunk  (cost=1615.94..1788.88 rows=155 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=1615.94..1615.94 rows=155 width=0)
              ->  Bitmap Index Scan on _hyper_2_370_chunk_vibration_gateway_channel_id_idx  (cost=0.00..409.11 rows=36010 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_370_chunk_vibration_sensor_id_idx  (cost=0.00..1206.50 rows=106542 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Index Scan using _hyper_2_330_chunk_vibration_sensor_id_idx on _hyper_2_330_chunk  (cost=0.56..2.79 rows=1 width=4)
        Index Cond: (sensor_id = 102)
        Filter: (("time" > '2021-12-24 14:00:00+09'::timestamp with time zone) AND (gateway_channel_id = 305))
  ->  Bitmap Heap Scan on _hyper_2_388_chunk  (cost=17506.33..25342.19 rows=7080 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=17506.33..17506.33 rows=7080 width=0)
              ->  Bitmap Index Scan on _hyper_2_388_chunk_vibration_gateway_channel_id_idx  (cost=0.00..4314.07 rows=380801 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_388_chunk_vibration_sensor_id_idx  (cost=0.00..13188.47 rows=1164534 width=0)
                    Index Cond: (sensor_id = 102)
  ->  Bitmap Heap Scan on _hyper_2_415_chunk  (cost=15441.87..24403.05 rows=8129 width=4)
        Recheck Cond: ((gateway_channel_id = 305) AND (sensor_id = 102))
        Filter: ("time" > '2021-12-24 14:00:00+09'::timestamp with time zone)
        ->  BitmapAnd  (cost=15441.87..15441.87 rows=8129 width=0)
              ->  Bitmap Index Scan on _hyper_2_415_chunk_vibration_gateway_channel_id_idx  (cost=0.00..3869.67 rows=341641 width=0)
                    Index Cond: (gateway_channel_id = 305)
              ->  Bitmap Index Scan on _hyper_2_415_chunk_vibration_sensor_id_idx  (cost=0.00..11567.88 rows=1021202 width=0)
                    Index Cond: (sensor_id = 102)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

 

by 마농 [2022.06.24 18:04:57]

인덱스가 각각 인 듯 합니다.
결합인덱스가 필요합니다.
조건 3개 항목 또는 조회항목까지 4개 항목 결합.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입