POSTED BY
2023-10-12

VSといってもRedisはメモリなんだから勝敗は必然として、実際両者どれくらいの位置情報データ抽出速度であるのかは、C言語のクライアントライブラリでSQL実行関数＝redisCommand()とPQexec()の前後でgettimeofday()を呼んだ値の比較でなければ真の実行速度を計測したとは言えない（と思う）。

【Redisメモ・６】GEORADIUS_RO関数をNode.jsから呼ぶ PostgreSQLでGEOGRAPHY検索をC言語から行う

前者はNode.jsなので計測値が本当の抽出速度かはわからないし、後者はCでもマッチしたデータに緯度経度をつけて返しているので、省略している前者と比べフェアではない。そもそも両者はイオンスタイル入間から10km圏内の駅検索をたった１回呼んでるだけなので実測もなにも不充分である。

よって、今回は総まとめとして、登録した全国駅9210件すべてについて、それぞれの半径５０ｋｍ圏内にある駅を２０件、駅名と直線距離を抽出する。RedisのC言語ライブラリhiredis-redisCommand、PostgresQLのC言語ライブラリlibpq-PQexecの両方を呼びそれぞれの実行時間をgettimeofdayで計測する。
（redisのhiredis使用準備はこちら、postgresqlのlibpq使用準備はこちら）

ソースコードは以下。

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <unistd.h>
#include <sys/time.h>
#include <hiredis/hiredis.h>
#include <postgresql/libpq-fe.h>

int main(int argc, char **argv) {

  redisContext *rdconn;
  redisReply *rdresp;
  PGconn *pgconn;
  PGresult *pgresp,*pgresp2;
  int i,n;
  int j,q;
  struct timeval tv_fr,tv_to;
  double tm_fr,tm_to;
  char sql[2048];
  char eki[512],lon[256],lat[256];
  double rd_total = 0,pg_total = 0;

  /* 接続 */
  rdconn = redisConnect("127.0.0.1", 6379);
  if (!rdconn) {
    fprintf(stderr, "redisConnect error\n");
    return -1;
  }
  if (rdconn->err) {
    fprintf(stderr, "%s\n", rdconn->errstr);
    redisFree(rdconn);
    return -1;
  }

  pgconn = PQconnectdb( "host=127.0.0.1 port=5432 dbname=ekidb" );
  if(!pgconn || PQstatus( pgconn ) == CONNECTION_BAD ){
    fprintf(stderr, "PQpgconnectdb error\n");
    if(pgconn){
      PQfinish(pgconn);
    }
    redisFree(rdconn);
    return (-1);
  }

  /* ランダムデータはPostgresから取得する */
  sprintf(sql,
/*  "SELECT name, ST_X(geom::geometry) AS longitude, ST_Y(geom::geometry) AS latitude FROM ekipos ORDER BY RANDOM() LIMIT 1000");*/
  "SELECT name, ST_X(geom::geometry) AS longitude, ST_Y(geom::geometry) AS latitude FROM ekipos"); /* 全駅バージョン */
  fprintf(stdout,"%s\n",sql);
  pgresp = PQexec(pgconn, sql);
  if(!pgresp || PQresultStatus( pgresp ) != PGRES_TUPLES_OK ){
    fprintf(stderr, "PQexec error");
    if(pgresp){
      fprintf(stderr," %s",PQresultErrorMessage( pgresp ) );
      PQclear(pgresp);
    }
    fprintf(stderr,"\n");
    PQfinish(pgconn);
    redisFree(rdconn);
    return (-1);
  }

  /* 駅半径50km以内の駅=正方形では100km四方=の駅を抽出するループ */

  for( i = 0,n = PQntuples( pgresp ); i < n; i++ ){
    strcpy(eki,PQgetvalue( pgresp,i,0 ));
    strcpy(lon,PQgetvalue( pgresp,i,1 ));
    strcpy(lat,PQgetvalue( pgresp,i,2 ));

    fprintf(stdout,"%d. %s --------------------\n",i + 1,eki);

    /* redis */
    sprintf(sql,
    "GEORADIUS_RO ekipos %s %s 50000 m WITHDIST COUNT 20 ASC",lon,lat);
    fprintf(stdout,"%s\n",sql);

    q = 0;

    gettimeofday(&tv_fr, NULL);
    rdresp = (redisReply *)redisCommand(rdconn, "GEORADIUS_RO ekipos %s %s 50000 m WITHDIST COUNT 20 ASC",lon,lat);
    gettimeofday(&tv_to, NULL);

    if (rdresp && rdresp->type != REDIS_REPLY_ERROR){
      q = rdresp->elements;
    }

    fprintf(stdout,"hit %d stations on Redis\n",q);

    fprintf(stdout,"unix time before select: %ld.%ld SEC\n",tv_fr.tv_sec,tv_fr.tv_usec);
    fprintf(stdout,"unix time after  select: %ld.%ld SEC\n",tv_to.tv_sec,tv_to.tv_usec);
    
    tm_fr = (((double)tv_fr.tv_sec)*((double)1000000)+((double)tv_fr.tv_usec));
    tm_to = (((double)tv_to.tv_sec)*((double)1000000)+((double)tv_to.tv_usec));

    fprintf(stdout,"diff time: %lf MICROSEC (%lf MSEC)\n",tm_to - tm_fr,(tm_to - tm_fr) / 1000);

    rd_total += (tm_to - tm_fr);

    if(q > 0){
      for(j = 0; j < q; j++){
        fprintf(stdout,"%s %sM\n",rdresp->element[j]->element[0]->str,rdresp->element[j]->element[1]->str);
      }
    }

    if(rdresp){
      freeReplyObject(rdresp);
    }

    /* postgresql */
    sprintf(sql,
    "SELECT name, ST_Distance('SRID=4326;POINT(%s %s)', geom) AS distance"
    " FROM ekipos WHERE ST_DWithin(geom, ST_GeographyFromText('SRID=4326;POINT(%s %s)'), 50000.0)"
    " ORDER BY distance LIMIT 20",lon,lat,lon,lat);
    fprintf(stdout,"%s\n",sql);

    q = 0;

    gettimeofday(&tv_fr, NULL);
    pgresp2 = PQexec(pgconn, sql);
    gettimeofday(&tv_to, NULL);

    if( pgresp2 && PQresultStatus( pgresp2 ) == PGRES_TUPLES_OK ){
      q = PQntuples( pgresp2 );
    }
  
    fprintf(stdout,"hit %d stations on PostgreSQL\n",q);

    fprintf(stdout,"unix time before select: %ld.%ld SEC\n",tv_fr.tv_sec,tv_fr.tv_usec);
    fprintf(stdout,"unix time after  select: %ld.%ld SEC\n",tv_to.tv_sec,tv_to.tv_usec);
    
    tm_fr = (((double)tv_fr.tv_sec)*((double)1000000)+((double)tv_fr.tv_usec));
    tm_to = (((double)tv_to.tv_sec)*((double)1000000)+((double)tv_to.tv_usec));

    fprintf(stdout,"diff time: %lf MICROSEC (%lf MSEC)\n",tm_to - tm_fr,(tm_to - tm_fr) / 1000);

    pg_total += (tm_to - tm_fr);

    if(q > 0){
      for(j = 0; j < q; j++){
        fprintf(stdout,"%s %sM\n",PQgetvalue( pgresp2,j,0 ),PQgetvalue( pgresp2,j,1 ) );
      }
    }

    if(pgresp2){
      PQclear(pgresp2);
    }
  }

  fprintf( stdout,"----------------------------------------\n"
  "Redis GEORADIUS_RO %d call TOTAL = %lf MSEC, AVG = %lf MSEC\n",n, rd_total / 1000,(rd_total / n) / 1000);

  fprintf( stdout,"----------------------------------------\n"
  "PostgreSQL SELECT_ST %d call TOTAL = %lf MSEC, AVG = %lf MSEC\n",n, pg_total / 1000,(pg_total / n) / 1000);

  PQclear(pgresp);
  PQfinish(pgconn);
  redisFree(rdconn);

  return 0;
}

・PostgreSQL上の駅データ9210全件を抽出して、それぞれ周辺駅検索をRedisとPostgreSQL両方について行っている。redisCommand,PQexecをgettimeofdayで囲み純粋なSQL実行速度を計っている。

・RedisのGEORADIUS_RO WITHDIST関数の戻り構造体がどう入るかはググっても意外と出てこないので参考になるやも。redisReply->elementsがヒット数、redisReply->elementがヒット数分あるが、戻る駅名・距離はさらにその子elementに入るのがポイント。
つまりヒット数redisReply->elementsが20であった場合、
先頭返却駅の駅名はredisReply->element[0]->element[0]
先頭返却駅の距離はredisReply->element[0]->element[1]
末尾返却駅の駅名はredisReply->element[19]->element[0]
末尾返却駅の距離はredisReply->element[19]->element[1]
に入っている。

コンパイル

gcc -o georadius_bench.x -I /usr/local/include georadius_bench.c -L /usr/local/lib -lpq -lhiredis

実行＆出力データをログに保存

./georadius_bench.x > georadius_bench.log

結果ログ抜粋

SELECT name, ST_X(geom::geometry) AS longitude, ST_Y(geom::geometry) AS latitude FROM ekipos
1. 函館-01 --------------------
GEORADIUS_RO ekipos 140.726413 41.773709 50000 m WITHDIST COUNT 20 ASC
hit 20 stations on Redis
unix time before select: 1588775420.953366 SEC
unix time after  select: 1588775420.953566 SEC
diff time: 200.000000 MICROSEC (0.200000 MSEC)
函館-01 0.1130M
函館駅前-01 177.4044M
市役所前-01 543.6873M
松風町-01 611.4958M
新川町-01 851.2134M
魚市場通-01 957.7585M
千歳町-01 1044.8368M
十字街-01 1263.2416M
昭和橋-01 1293.3781M
末広町-01 1417.5486M
大町-01 1479.8621M
宝来町-01 1507.2891M
堀川町-01 1635.1121M
函館どつく前-01 1828.5129M
青柳町-01 2058.1419M
千代台-01 2177.8014M
谷地頭-01 2419.5933M
中央病院前-01 2528.2055M
五稜郭公園前-01 2725.3475M
杉並町-01 3196.7587M
SELECT name, ST_Distance('SRID=4326;POINT(140.726413 41.773709)', geom) AS distance FROM ekipos WHERE ST_DWithin(geom, ST_GeographyFromText('SRID=4326;POINT(140.726413 41.773709)'), 50000.0) ORDER BY distan
ce LIMIT 20
hit 20 stations on PostgreSQL
unix time before select: 1588775420.953584 SEC
unix time after  select: 1588775420.955281 SEC
diff time: 1697.000000 MICROSEC (1.697000 MSEC)
函館-01 0M
函館駅前-01 177.32031897M
市役所前-01 543.15095598M
松風町-01 612.65155546M
新川町-01 853.34963773M
魚市場通-01 956.98566345M
千歳町-01 1047.20680239M
十字街-01 1262.8742989M
昭和橋-01 1295.48228611M
末広町-01 1419.10563232M
大町-01 1483.10443094M
宝来町-01 1506.10281579M
堀川町-01 1637.41650764M
函館どつく前-01 1832.56452565M
青柳町-01 2055.95692647M
千代台-01 2180.39210839M
谷地頭-01 2417.41850219M
中央病院前-01 2530.67571891M
五稜郭公園前-01 2727.71741963M
杉並町-01 3200.78113404M
2. 五稜郭-01 --------------------
....
（中略...駅の数だけ9210回...）
....
9210. 関門海峡めかり-40 --------------------
GEORADIUS_RO ekipos 130.967347 33.960627 50000 m WITHDIST COUNT 20 ASC
hit 20 stations on Redis
unix time before select: 1588775446.702667 SEC
unix time after  select: 1588775446.702993 SEC
diff time: 326.000000 MICROSEC (0.326000 MSEC)
関門海峡めかり-40 0.2584M
ノーフォーク広場-40 591.1132M
出光美術館-40 1440.0446M
門司港-40 1803.7565M
九州鉄道記念館-40 1861.6866M
下関-35 4245.9263M
幡生-35 4365.1211M
新下関-35 5315.5277M
小森江-40 5590.4088M
綾羅木-35 6105.8535M
門司-40 7002.1836M
梶栗郷台地-35 7051.3222M
長府-35 7164.8084M
安岡-35 8544.7029M
福江-35 10737.3868M
小倉-40 11374.5986M
平和通-40 11721.4739M
西小倉-40 11765.9644M
旦過-40 11995.3954M
香春口三萩野-40 12600.7803M
SELECT name, ST_Distance('SRID=4326;POINT(130.967347 33.960627)', geom) AS distance FROM ekipos WHERE ST_DWithin(geom, ST_GeographyFromText('SRID=4326;POINT(130.967347 33.960627)'), 50000.0) ORDER BY distance LIMIT 20
hit 20 stations on PostgreSQL
unix time before select: 1588775446.703004 SEC
unix time after  select: 1588775446.704371 SEC
diff time: 1367.000000 MICROSEC (1.367000 MSEC)
関門海峡めかり-40 0M
ノーフォーク広場-40 590.10448289M
出光美術館-40 1436.29440586M
門司港-40 1799.71585371M
九州鉄道記念館-40 1857.07912085M
下関-35 4252.60595014M
幡生-35 4367.81268514M
新下関-35 5303.82934617M
小森江-40 5580.80186376M
綾羅木-35 6099.74781382M
門司-40 6989.45047599M
梶栗郷台地-35 7042.63903746M
長府-35 7150.79975162M
安岡-35 8533.07229233M
福江-35 10718.0068179M
小倉-40 11332.87815442M
平和通-40 11714.31731227M
西小倉-40 11762.91353657M
旦過-40 11987.44961612M
香春口三萩野-40 12589.78931416M
----------------------------------------
Redis GEORADIUS_RO 9210 call TOTAL = 4351.278000 MSEC, AVG = 0.472451 MSEC
----------------------------------------
PostgreSQL SELECT_ST 9210 call TOTAL = 21015.992000 MSEC, AVG = 2.281867 MSEC

それぞれの実行時間も出力しているが、最後にトータルでかかった総SELECT時間とその平均値を出している。RedisはredisCommand実行平均速度＝0.47ミリ秒、PostgreSQLはPQexec実行平均速度＝2.28ミリ秒と結論が出ました。とはいえこれでも検索対象がたかだか１万件程度に過ぎないので、今後時間を見つけて数百万件程度の位置情報で試してみたいところです。