By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 6
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE INDEX
CREATE INDEX
| unique_id | sender | message | msisdn_receiver | country_code | id | sim | status | uaId | smsId |
|---|---|---|---|---|---|---|---|---|---|
| d03676da3fea8c58 | eb 543277 UE | +45675296242 | AF | 13579877 | msisdn_2 | delivered | 2259 | 13579877 | |
| d03676da3fea8c58 | دrJbA/XP1K+V | +34875296242 | AF | 13579876 | msisdn_2 | delivered | 2259 | 13579876 | |
| bd6e32a4c1c29b22 | SWfo 851706 | +832192078809 | AL | 13579875 | msisdn_1 | delivered | 2227 | 13579875 | |
| d1154bfd00b0d1f1 | CK 2300 KA | +8343767804889 | NP | 13579873 | msisdn_2 | delivered | 1918 | 13579873 | |
| 43d4126cd5be2624 | 748824 | +123473114505 | AL | 13579867 | msisdn_1 | delivered | 2237 | 13579867 | |
| 4412821353cf63e3 | WcBO 306076 | +123497224823 | AL | 13579862 | msisdn_1 | delivered | 2228 | 13579862 | |
| bd6e32a4c1c29b22 | iYlN 289637 | +123493501022 | AL | 13579857 | msisdn_1 | delivered | 2227 | 13579857 | |
| 010dcdcfe2719c39 | Li 972000 pP | +4563862372418 | NP | 13579854 | msisdn_1 | delivered | 2353 | 13579854 | |
| 010dcdcfe2719c39 | fc 7922 Sp | +3784840837003 | NP | 13579853 | msisdn_2 | delivered | 2353 | 13579853 | |
| 43d4126cd5be2624 | qIKmG 128055 | +763495182675 | AL | 13579848 | msisdn_1 | delivered | 2237 | 13579848 |
SELECT 10
SET
| QUERY PLAN |
|---|
| Limit (cost=42.99..42.99 rows=2 width=2470) |
| -> Sort (cost=42.99..42.99 rows=2 width=2470) |
| Sort Key: s.id DESC |
| -> Unique (cost=42.92..42.98 rows=2 width=2470) |
| -> Sort (cost=42.92..42.93 rows=2 width=2470) |
| Sort Key: u.unique_id, s.sender, s.message, s.msisdn_receiver, s.country_code, s.id, s.sim, s.status, u.id, s.id |
| -> Append (cost=8.14..42.91 rows=2 width=2470) |
| -> Nested Loop Left Join (cost=8.14..22.71 rows=1 width=2470) |
| -> Bitmap Heap Scan on sms s (cost=8.00..12.27 rows=1 width=2066) |
| Recheck Cond: (immutable_concat_ws('|'::text, VARIADIC ARRAY[(sender)::text, (message)::text, (msisdn_receiver)::text, (country_code)::text, (id)::text, (sim)::text, (status)::text]) ~* '135'::text) |
| Filter: ((type)::text = 'sms'::text) |
| -> Bitmap Index Scan on sms_multi_trgm_gin_index (cost=0.00..8.00 rows=1 width=0) |
| Index Cond: (immutable_concat_ws('|'::text, VARIADIC ARRAY[(sender)::text, (message)::text, (msisdn_receiver)::text, (country_code)::text, (id)::text, (sim)::text, (status)::text]) ~* '135'::text) |
| -> Index Scan using user_apps_pkey on user_apps u (cost=0.13..8.15 rows=1 width=404) |
| Index Cond: (id = s.user_app_id) |
| -> Nested Loop (cost=8.14..20.18 rows=1 width=2470) |
| -> Bitmap Heap Scan on user_apps u_1 (cost=8.00..12.01 rows=1 width=404) |
| Recheck Cond: ((unique_id)::text ~* '135'::text) |
| -> Bitmap Index Scan on user_apps_unique_id_trgm_gin_index (cost=0.00..8.00 rows=1 width=0) |
| Index Cond: ((unique_id)::text ~* '135'::text) |
| -> Index Scan using sms_user_app_id_index on sms s_1 (cost=0.14..8.15 rows=1 width=2066) |
| Index Cond: (user_app_id = u_1.id) |
| Filter: ((type)::text = 'sms'::text) |
EXPLAIN