0

I have a ticketing_system table and I try the insertion of 10+ million rows. So this cost 50 seconds. But I need almost a max of 10-15 seconds.

CREATE TABLE ticketing_system (
  ticket_id UUID not null default uuid_generate_v4(),
  count int,
);

This is a table structure. This is a table structure. I just use this query.

INSERT INTO ticketing_system(count) 
select * from generate_series(1,10000000);

This query inserts data in almost 50 seconds.

explain analyze INSERT INTO ticketing_system(count) select * from generate_series(1,10000000);
                                                                  QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Insert on ticketing_system  (cost=0.00..125000.00 rows=0 width=0) (actual time=52620.980..52620.981 rows=0 loops=1)
   ->  Function Scan on generate_series  (cost=0.00..125000.00 rows=10000000 width=20) (actual time=765.900..44678.638 rows=10000000 loops=1)
 Planning Time: 0.056 ms
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions authentic, Deforming true
   Timing: Generation 0.578 ms, Inlining 0.000 ms, Optimization 0.174 ms, Emission 1.199 ms, Total 1.952 ms
 Execution Time: 52631.718 ms
(8 rows)

How to optimize this query? I dropped indexes.

I solved it.

So I used the Citus

I created the worker and coordinator with citus.

Then I created the table in Postgresql and used the

SELECT create_distributed_table('table_name', 'field_name');.

Citus created the table on workers and When I inserted 100 million rows, it cost 10 seconds.

8
  • Please enable track_io_timing and run EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL) on the statement. Sep 23 at 13:49
  • 2
    How long does explain (analyze) select * from generate_series(1,10000000); take? Sep 23 at 14:04
  • ^agreed with a_horse_with_no_name, I'd be more curious on how long it takes to generate the 1 million rows before the INSERT, which I assume is more so your issue.
    – J.D.
    Sep 23 at 14:06
  • Where has this requirement come from? Are you expecting to need to insert generated data constantly? Or will this data come from a wide variety of things talking to your database and this is the rate that you expect data to come in? Sep 23 at 14:42
  • Based on how long it takes to just generate and throw away 10e6 random UUID to start with, I'd start with the assumption you won't be able to make this plan work at all, and come up with another plan. Maybe you can pre-create some tables and then attach as partitions.
    – jjanes
    Sep 23 at 16:23

0

Your Answer

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