1

So this is my first experience with big data. I have a ticketing_system table, and I I inserted one milliard (one billion) of fake data to the table.

CREATE TABLE ticketing_system (
    id BIGSERIAL,
    ticket_id UUID not null default uuid_generate_v4(),
    count int,
    created_at timestamptz NOT NULL
);

I need a Postgresql response of about 10-30 ms when searching the ticket_id with where clause.I created BRIN index CREATE INDEX in_ticketing_system_brin ON ticketing_system USING brin(ticket_id);. But this isn't helping me.

explain analyze select * from ticketing_system where ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99';
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1217.39..1854198.38 rows=1 width=36) (actual time=997.056..84730.718 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Bitmap Heap Scan on ticketing_system  (cost=217.39..1853198.28 rows=1 width=36) (actual time=56707.851..84601.029 rows=0 loops=3)
         Recheck Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
         Rows Removed by Index Recheck: 46103039
         Heap Blocks: lossy=366097
         ->  Bitmap Index Scan on in_ticketing_system_brin  (cost=0.00..217.39 rows=3544816 width=0) (actual time=153.203..153.209 rows=11525760 loops=1)
               Index Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
 Planning Time: 3.167 ms
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 12.892 ms, Inlining 491.333 ms, Optimization 92.106 ms, Emission 91.551 ms, Total 687.882 ms
 Execution Time: 84833.538 ms
(15 rows)
15
  • 4
    Have you tried a simple B-tree index instead? BRIN doesn't seem to be a good fit for your use case. What's the reason for both id and ticket_id to exist?
    – mustaccio
    Sep 20 at 11:53
  • 1
    hi, @mustaccio I try now. Sep 20 at 11:55
  • 1
    @mustaccio Thank u for helping. The response is 0.950ms. But I have another question. Btree index uses high memory. postgresUser=# SELECT pg_size_pretty(pg_relation_size('in_ticketing_system_btree')); pg_size_pretty ---------------- 2963 MB (1 row) postgresUser=# SELECT pg_size_pretty(pg_relation_size('in_ticketing_system_ticket_id_btree')); pg_size_pretty ---------------- 4161 MB (1 row) Sep 20 at 12:05
  • 3
    That is disk space, not "memory" (which usually refers to RAM). And yes, a btree indexes uses a lot of disk space. Sometimes that is the cost of performance.
    – jjanes
    Sep 20 at 15:54
  • 3
    "I need low memory usage and high performance" — this is usually known as "magic." Sep 20 at 20:58

2 Answers 2

3

A BRIN index is no good fit in your case, and you need a B-tree index:

CREATE INDEX ON ticketing_system (ticket_id);

That index will be much larger than a BRIN index, but it won't be any strain on your RAM. Regardless of the size of the index, an index scan will only use negligible amounts of memory. All tables and indexes are stored and cached in units of 8kB, and an index scan won't have to touch many of these.

4

A BRIN index works by recording the minimum and maximum of block ranges. Therefore, it does not help unless the data have some kinds of tendency, such as being clustered. In your case, both HASH or BTREE works. I believe a HASH index has a smaller memory footprint when the number of data goes large, but be aware of its limitations (e.g. PostgreSQL does not support multicolumn HASH indexes).

Another way is to use integer IDs instead of UUIDs and create them in order. Your SERIAL already works as a primary key, but depending on the meaning of your columns, it may or may not make sense to select the data filtered by id only.

3
  • 1
    Hi, Thank u for your answer. I will try the HASH method. You say "PostgreSQL does not support multicolumn HASH indexes". What does it mean? Do you mean a lot of columns in one table? Sep 20 at 13:26
  • 2
    If your WHERE-clause involves more than one columns, you will need a multicolumn index. Of course the database query planner can decide to use a single column index and filter with another condition on the fly, or use two single column indexes and perform a bitmap index scan. However, both of them will be slower. I suggest looking at postgresql.org/docs/current/indexes-multicolumn.html. Sep 20 at 13:49
  • 1
    Thank you for your answer. But this solution doesn't help me. Because the multicolumn index's response to me 18second, but I use the BTREE index, the query's response to me 1ms. Sep 20 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.