Here is my query, it takes 2.7 sec:
SELECT t2.id, t1.id FROM sample t1
INNER JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex;
The associated EXPLAIN (ANALYSE,BUFFERS)
output:
Nested Loop (cost=0.00..1108.30 rows=356 width=8) (actual time=56.130..2740.906 rows=33 loops=1)
Join Filter: (t1.data ~* regex_set.regex)
Rows Removed by Join Filter: 65967
Buffers: local hit=18
-> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.014..1.534 rows=2000 loops=1)
Buffers: local hit=17
-> Materialize (cost=0.00..1.08 rows=33 width=36) (actual time=0.000..0.004 rows=33 loops=2000)
Buffers: local hit=1
-> Limit (cost=0.00..0.59 rows=33 width=36) (actual time=0.005..0.014 rows=33 loops=1)
Buffers: local hit=1
-> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.004..0.008 rows=33 loops=1)
Buffers: local hit=1
Planning time: 0.129 ms
Execution time: 2740.952 ms
The same query with limit set to 32 takes only 0.25 sec with same plan (there is nothing special with row 33: tested limit 32 offset 1
, had the same result as below):
Nested Loop (cost=0.00..1075.88 rows=345 width=8) (actual time=5.871..255.315 rows=32 loops=1)
Join Filter: (t1.data ~* regex_set.regex)
Rows Removed by Join Filter: 63968
Buffers: local hit=18
-> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.008..0.498 rows=2000 loops=1)
Buffers: local hit=17
-> Materialize (cost=0.00..1.05 rows=32 width=36) (actual time=0.000..0.002 rows=32 loops=2000)
Buffers: local hit=1
-> Limit (cost=0.00..0.57 rows=32 width=36) (actual time=0.003..0.013 rows=32 loops=1)
Buffers: local hit=1
-> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.003..0.006 rows=32 loops=1)
Buffers: local hit=1
Planning time: 0.109 ms
Execution time: 255.374 ms
Here is the initialisation script I used for these tests.
DO $$
DECLARE s_length INT=2000;
DECLARE r_length INT=50;
BEGIN
DROP TABLE IF EXISTS sample;
CREATE TEMP TABLE sample AS
SELECT generate_series(1, s_length) id, md5(random() :: text) AS data;
DROP TABLE IF EXISTS regex_set;
CREATE TEMP TABLE regex_set AS
SELECT g.id, s.data regex
FROM generate_series(1, r_length) g (id),
sample s
WHERE s.id = round(g.id * s_length / r_length);
END $$;
With my real data, I had a delay at exactly the same 33 limit
(the time difference was 1 to 30).
I'm running Postgres 10 on Docker container (2 cpu / 2GB RAM) with following (arbitrary) edits to default postgres.conf (didn't have much effect):
shared_buffers = 512MB
temp_buffers = 32MB
work_mem = 16MB (also set to 64MB, had no effect)
Equivalent query is a lot faster on default MySQL 5.8 container.
Is there any Postgres master that could explain what is happening?
work_mem
help? If yes, it would prove that it's a memory thingperf
to profile what the slow case is spending its time doing? Or usetop
to see if it is CPU bound versus something else (IO due to swapping, for example)?with a2 as (select regex from t2 LIMIT 33) SELECT t1.id FROM t1 join a2 ON t1.descr ~* a2.regex;
But it would be interesting to find out what the root cause is. Maybe you should post that on the performance mailing list the devs might have more ideas on how debug this.debug symbols
andperf
, I will look into it when I have time. Top indicates: swap not used, cpu at 100%. Luckily, I could reproduce the problem with generic data, and surprisingly I get the delay at the samelimit 33
. Updated my question accordingly.