I have a select query that has the below where clause.
where lpad(a.x,10,'0') = lpad(b.y,10,'0')
This doesn't use the indices I have on x and y. So I created the below indexes too.
create index x_idx ON table_a USING btree(lpad(x, 10, '0'));
create index y_idx ON table_b USING btree(lpad(y, 10, '0'));
Still, the above query doesn't use the indexes.
What am I missing here? Is it not possible to create indices on functions like lpad?
Update 1: Here is a complete sample.
Please note that I had to make this sample a bit complex to match my real case as it seemed the simple case worked fine.
CREATE TABLE sellers (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
city_id VARCHAR ( 255 ) NOT NULL
);
CREATE TABLE buyers (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
city_id VARCHAR ( 255 ) NOT NULL
);
CREATE TABLE sellers_2 (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
city_id VARCHAR ( 255 ) NOT NULL
);
CREATE TABLE buyers_2 (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
city_id VARCHAR ( 255 ) NOT NULL
);
EXPLAIN(ANALYZE, VERBOSE, BUFFERS)
select *
from
(select s.city_id as s_c, b.city_id as b_c, * from sellers s
join buyers b on lpad(s.city_id, 10, '0') = lpad(b.city_id, 10, '0')) one
join
(select s2.city_id as s2_c, b2.city_id as b2_c, * from sellers_2 s2 inner
join buyers_2 b2 on lpad(s2.city_id, 10, '0') = lpad(b2.city_id, 10, '0')) two
on lpad(one.s_c, 10, '0') = lpad(two.s2_c, 10, '0');
Create indices:
create index seller_city_idx on sellers USING btree (lpad(city_id, 10, '0'));
create index buyer_city_idx on buyers USING btree (lpad(city_id, 10, '0'));
create index seller2_city_idx on sellers_2 USING btree (lpad(city_id, 10, '0'));
create index buyer2_city_idx on buyers_2 USING btree (lpad(city_id, 10, '0'));
After indices:
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=3.41..4.76 rows=6 width=4616) (actual time=0.120..0.129 rows=6 loops=1) |
Output: s.city_id, b.city_id, s.user_id, s.username, s.city_id, b.user_id, b.username, b.city_id, s2.city_id, b2.city_id, s2.user_id, s2.username, s2.city_id, b2.user_id, b2.username, b2.city_id|
Hash Cond: (lpad((s.city_id)::text, 10, '0'::text) = lpad((b2.city_id)::text, 10, '0'::text)) |
Buffers: shared hit=4 |
-> Hash Join (cost=2.27..3.53 rows=6 width=1914) (actual time=0.044..0.050 rows=6 loops=1) |
Output: s.city_id, s.user_id, s.username, b.city_id, b.user_id, b.username, s2.city_id, s2.user_id, s2.username |
Hash Cond: (lpad((s.city_id)::text, 10, '0'::text) = lpad((s2.city_id)::text, 10, '0'::text)) |
Buffers: shared hit=3 |
-> Hash Join (cost=1.14..2.29 rows=6 width=1276) (actual time=0.025..0.029 rows=6 loops=1) |
Output: s.city_id, s.user_id, s.username, b.city_id, b.user_id, b.username |
Hash Cond: (lpad((s.city_id)::text, 10, '0'::text) = lpad((b.city_id)::text, 10, '0'::text)) |
Buffers: shared hit=2 |
-> Seq Scan on public.sellers s (cost=0.00..1.06 rows=6 width=638) (actual time=0.007..0.008 rows=6 loops=1) |
Output: s.user_id, s.username, s.city_id |
Buffers: shared hit=1 |
-> Hash (cost=1.06..1.06 rows=6 width=638) (actual time=0.011..0.011 rows=6 loops=1) |
Output: b.city_id, b.user_id, b.username |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
Buffers: shared hit=1 |
-> Seq Scan on public.buyers b (cost=0.00..1.06 rows=6 width=638) (actual time=0.007..0.008 rows=6 loops=1) |
Output: b.city_id, b.user_id, b.username |
Buffers: shared hit=1 |
-> Hash (cost=1.06..1.06 rows=6 width=638) (actual time=0.013..0.013 rows=6 loops=1) |
Output: s2.city_id, s2.user_id, s2.username |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
Buffers: shared hit=1 |
-> Seq Scan on public.sellers_2 s2 (cost=0.00..1.06 rows=6 width=638) (actual time=0.008..0.008 rows=6 loops=1) |
Output: s2.city_id, s2.user_id, s2.username |
Buffers: shared hit=1 |
-> Hash (cost=1.06..1.06 rows=6 width=638) (actual time=0.069..0.069 rows=6 loops=1) |
Output: b2.city_id, b2.user_id, b2.username |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
Buffers: shared hit=1 |
-> Seq Scan on public.buyers_2 b2 (cost=0.00..1.06 rows=6 width=638) (actual time=0.025..0.026 rows=6 loops=1) |
Output: b2.city_id, b2.user_id, b2.username |
Buffers: shared hit=1 |
Planning time: 1.665 ms |
Execution time: 0.199 ms |
My question is about one JOIN two.
Update 2: with 100k data
Before indices:
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Merge Join (cost=39743.28..250002416743.28 rows=12500000000000 width=88) (actual time=1501.115..1710.061 rows=6545 loops=1) |
Output: s.city_id, b.city_id, s.user_id, s.username, s.city_id, b.user_id, b.username, b.city_id, s2.city_id, b2.city_id, s2.user_id, s2.username, s2.city_id, b2.user_id, b2.username, b2.city_id|
Merge Cond: (lpad((s.city_id)::text, 10, '0'::text) = lpad((s2.city_id)::text, 10, '0'::text)) |
Buffers: shared hit=2529, temp read=3282 written=3128 |
-> Merge Join (cost=19871.64..1020871.64 rows=50000000 width=32) (actual time=752.228..838.543 rows=40014 loops=1) |
Output: s.city_id, s.user_id, s.username, b.city_id, b.user_id, b.username |
Merge Cond: ((lpad((s.city_id)::text, 10, '0'::text)) = (lpad((b.city_id)::text, 10, '0'::text))) |
Buffers: shared hit=1267, temp read=1630 written=1564 |
-> Sort (cost=9935.82..10185.82 rows=100000 width=16) (actual time=360.257..383.275 rows=100000 loops=1) |
Output: s.city_id, s.user_id, s.username, (lpad((s.city_id)::text, 10, '0'::text)) |
Sort Key: (lpad((s.city_id)::text, 10, '0'::text)) |
Sort Method: external merge Disk: 3904kB |
Buffers: shared hit=636, temp read=488 written=489 |
-> Seq Scan on public.sellers s (cost=0.00..1631.00 rows=100000 width=16) (actual time=0.019..20.382 rows=100000 loops=1) |
Output: s.city_id, s.user_id, s.username, lpad((s.city_id)::text, 10, '0'::text) |
Buffers: shared hit=631 |
-> Sort (cost=9935.82..10185.82 rows=100000 width=16) (actual time=391.962..403.480 rows=106929 loops=1) |
Output: b.city_id, b.user_id, b.username, (lpad((b.city_id)::text, 10, '0'::text)) |
Sort Key: (lpad((b.city_id)::text, 10, '0'::text)) |
Sort Method: external sort Disk: 4296kB |
Buffers: shared hit=631, temp read=1108 written=1075 |
-> Seq Scan on public.buyers b (cost=0.00..1631.00 rows=100000 width=16) (actual time=0.029..20.583 rows=100000 loops=1) |
Output: b.city_id, b.user_id, b.username, lpad((b.city_id)::text, 10, '0'::text) |
Buffers: shared hit=631 |
-> Materialize (cost=19871.64..1145871.64 rows=50000000 width=32) (actual time=748.780..841.877 rows=42013 loops=1) |
Output: s2.city_id, s2.user_id, s2.username, b2.city_id, b2.user_id, b2.username |
Buffers: shared hit=1262, temp read=1652 written=1564 |
-> Merge Join (cost=19871.64..1020871.64 rows=50000000 width=32) (actual time=748.769..835.644 rows=39909 loops=1) |
Output: s2.city_id, s2.user_id, s2.username, b2.city_id, b2.user_id, b2.username |
Merge Cond: ((lpad((s2.city_id)::text, 10, '0'::text)) = (lpad((b2.city_id)::text, 10, '0'::text))) |
Buffers: shared hit=1262, temp read=1652 written=1564 |
-> Sort (cost=9935.82..10185.82 rows=100000 width=16) (actual time=356.547..379.828 rows=99998 loops=1) |
Output: s2.city_id, s2.user_id, s2.username, (lpad((s2.city_id)::text, 10, '0'::text)) |
Sort Key: (lpad((s2.city_id)::text, 10, '0'::text)) |
Sort Method: external merge Disk: 3904kB |
Buffers: shared hit=631, temp read=488 written=489 |
-> Seq Scan on public.sellers_2 s2 (cost=0.00..1631.00 rows=100000 width=16) (actual time=0.026..20.087 rows=100000 loops=1) |
Output: s2.city_id, s2.user_id, s2.username, lpad((s2.city_id)::text, 10, '0'::text) |
Buffers: shared hit=631 |
-> Sort (cost=9935.82..10185.82 rows=100000 width=16) (actual time=392.207..403.950 rows=107019 loops=1) |
Output: b2.city_id, b2.user_id, b2.username, (lpad((b2.city_id)::text, 10, '0'::text)) |
Sort Key: (lpad((b2.city_id)::text, 10, '0'::text)) |
Sort Method: external sort Disk: 4296kB |
Buffers: shared hit=631, temp read=1119 written=1075 |
-> Seq Scan on public.buyers_2 b2 (cost=0.00..1631.00 rows=100000 width=16) (actual time=0.032..20.954 rows=100000 loops=1) |
Output: b2.city_id, b2.user_id, b2.username, lpad((b2.city_id)::text, 10, '0'::text) |
Buffers: shared hit=631 |
Planning time: 0.729 ms |
Execution time: 1719.682 ms | |
Create indices:
create index seller_city_idx on sellers USING btree (lpad(city_id, 10, '0'));
create index buyer_city_idx on buyers USING btree (lpad(city_id, 10, '0'));
create index seller2_city_idx on sellers_2 USING btree (lpad(city_id, 10, '0'));
create index buyer2_city_idx on buyers_2 USING btree (lpad(city_id, 10, '0'));
After indices:
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Merge Join (cost=1.67..36697.70 rows=238630 width=88) (actual time=0.417..432.964 rows=6545 loops=1) |
Output: s.city_id, b.city_id, s.user_id, s.username, s.city_id, b.user_id, b.username, b.city_id, s2.city_id, b2.city_id, s2.user_id, s2.username, s2.city_id, b2.user_id, b2.username, b2.city_id|
Merge Cond: (lpad((s.city_id)::text, 10, '0'::text) = lpad((b.city_id)::text, 10, '0'::text)) |
Buffers: shared hit=401391 |
-> Merge Join (cost=0.83..15503.24 rows=133622 width=32) (actual time=0.060..196.892 rows=39973 loops=1) |
Output: s.city_id, s.user_id, s.username, b2.city_id, b2.user_id, b2.username |
Merge Cond: (lpad((s.city_id)::text, 10, '0'::text) = lpad((b2.city_id)::text, 10, '0'::text)) |
Buffers: shared hit=200678 |
-> Index Scan using seller_city_idx on public.sellers s (cost=0.42..6088.39 rows=100000 width=16) (actual time=0.003..47.076 rows=100000 loops=1) |
Output: s.user_id, s.username, s.city_id |
Buffers: shared hit=100347 |
-> Materialize (cost=0.42..6242.40 rows=100000 width=16) (actual time=0.010..60.183 rows=107144 loops=1) |
Output: b2.user_id, b2.username, b2.city_id |
Buffers: shared hit=100331 |
-> Index Scan using buyer2_city_idx on public.buyers_2 b2 (cost=0.42..5992.40 rows=100000 width=16) (actual time=0.005..47.293 rows=99999 loops=1) |
Output: b2.user_id, b2.username, b2.city_id |
Buffers: shared hit=100331 |
-> Materialize (cost=0.83..15894.00 rows=134467 width=32) (actual time=0.041..203.895 rows=42294 loops=1) |
Output: b.city_id, b.user_id, b.username, s2.city_id, s2.user_id, s2.username |
Buffers: shared hit=200713 |
-> Merge Join (cost=0.83..15557.83 rows=134467 width=32) (actual time=0.039..197.896 rows=40144 loops=1) |
Output: b.city_id, b.user_id, b.username, s2.city_id, s2.user_id, s2.username |
Merge Cond: (lpad((s2.city_id)::text, 10, '0'::text) = lpad((b.city_id)::text, 10, '0'::text)) |
Buffers: shared hit=200713 |
-> Index Scan using seller2_city_idx on public.sellers_2 s2 (cost=0.42..6092.34 rows=100000 width=16) (actual time=0.005..46.555 rows=100000 loops=1) |
Output: s2.user_id, s2.username, s2.city_id |
Buffers: shared hit=100360 |
-> Materialize (cost=0.42..6346.41 rows=100000 width=16) (actual time=0.007..60.809 rows=107087 loops=1) |
Output: b.user_id, b.username, b.city_id |
Buffers: shared hit=100353 |
-> Index Scan using buyer_city_idx on public.buyers b (cost=0.42..6096.41 rows=100000 width=16) (actual time=0.005..47.890 rows=100000 loops=1) |
Output: b.user_id, b.username, b.city_id |
Buffers: shared hit=100353 |
Planning time: 4.604 ms |
Execution time: 433.255 ms |
Now it looks like the indices have been used.
I'm still learning to read the explain output. So I have a question, have the indices been used for the one join two on lpad(one.s_c, 10, '0') = lpad(two.s2_c, 10, '0') operation?