PostgreSQLに770万件のデータを突っ込んでindexのサイズを調べてみた

psql (PostgreSQL) 9.4.1 vpsのmongodbにTwitterのログデータ770万件突っ込んでるんだけどselelctが遅すぎて全く使い物にならない。 投稿者のid、投稿日時にインデックス貼ってるんだけどそれすら使い物にならない。

MongoDBを真面目に使ってみた - ひよくあブログ collStats — MongoDB Manual 3.0 dbStats — MongoDB Manual 3.0

db.tweets.stats(1024*1024)
{
    "ns" : "twitter.tweets",
    "count" : 7771002, //ドキュメント数
    "size" : 25005,//容量。25GB
    "avgObjSize" : 3374,
    "storageSize" : 26104,
    "numExtents" : 33,
    "nindexes" : 9,
    "lastExtentSize" : 2046,
    "paddingFactor" : 1,
    "systemFlags" : 0,
    "userFlags" : 0,
    "totalIndexSize" : 2813,//全てのインデックスの合計サイズ 2.8GB
    "indexSizes" : {
        "_id_" : 239,
        "data_unique_key" : 438,//NumberLong(645577000000000000)
        "data_created_at" : 237,//NumberLong(1442752683000)
        "message_type" : 246,//"tweet"
        "data_user_id" : 334,//NumberLong(645577000000000000)
        "data_entities_user_mentions_id" : 283,//NumberLong(645577000000000000)
        "data_retweet_count" : 313,//NumberInt(100)
        "data_favorite_count" : 344,//NumberInt(100)
        "tweet_id" : 376 //NumberLong(645577000000000000)
    },
    "ok" : 1
}

300MBの場合、1レコードあたり40byte

んでbigintとtimestampとenumの項目を作って7770000件のレコードを入れて試してみた。Disk Usage - PostgreSQL wikiを参考に。

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first

name      owner    size
--------------------------
postgres  postgres 1270 MB

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
 
relation                       size
-------------------------------------
index_disk_size_test.test      387 MB //データベース
index_disk_size_test.enu_non   211 MB //インデックス 非ユニーク
index_disk_size_test.index_non 167 MB //インデックス 非ユニーク
index_disk_size_test.ts_non    167 MB //インデックス 非ユニーク
index_disk_size_test.ts_unq    166 MB //インデックス ユニーク
index_disk_size_test.index_unq 166 MB //インデックス ユニーク

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
relation                  total_size
------------------------------------
index_disk_size_test.test 1264 MB //↑の387+211～の合計と同じ

enumは211MB/7770000≒28byte timestampとbigintは166MB≒22byte

容量は減ってるけどあんま劇的ではない・・・1/10とかになってくれればいいのに。777万件のレコードは個人が扱うには多すぎるんだろうか・・・ いやいや現時点のmongoDBシステムの問題点は検索がくっそ遅いことが原因であってそっちも調べないと。本データをコピーしよっかなあ