Postgres 10

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 user_apps
(
    unique_id               varchar(191)                                                            not null,
    is_active               boolean      default false                                              not null,
    msisdn_1                varchar(191),
    msisdn_2                varchar(191),
    sms_per_day_1           integer      default 0,
    sms_per_day_2           integer      default 0,
    country_code            varchar(191),
    user_id                 integer,
    id                      serial
        primary key,
    created_at              timestamp(0),
    updated_at              timestamp(0),
    firebase_token          varchar(191),
    ip                      varchar(191),
    connection_date         timestamp(0) default '2022-08-10 22:24:45'::timestamp without time zone not null,
    description             varchar(191),
    date_of_reset_msisdn_1  timestamp(0),
    date_of_reset_msisdn_2  timestamp(0),
    version_of_package      varchar(191),
    is_suspended            boolean      default false                                              not null,
    is_passed               boolean      default false                                              not null,
    is_sent_email           boolean      default false                                              not null,
    msisdn_1_is_blocked     boolean      default false                                              not null,
    msisdn_2_is_blocked     boolean      default false                                              not null,
    block_sim_1_time        timestamp(0),
    block_sim_2_time        timestamp(0),
    unblock_msisdn_1_time   timestamp(0),
    unblock_msisdn_2_time   timestamp(0),
    msisdn_1_is_in_group    boolean      default false                                              not null,
    msisdn_2_is_in_group    boolean      default false                                              not null,
    msisdn_1_operator_group varchar(191),
    msisdn_2_operator_group varchar(191),
    disconnected_at         timestamp(0),
    delay_sms_send          integer      default 3,
    delay_is_connected      integer      default 900,
    delay_is_keep_alive     integer      default 120,
    is_connected            boolean      default false,
    is_keep_alive           boolean      default false,
    protocol_min            varchar(191) default '1.0'::character varying,
    connection_type         varchar(191),
    url                     varchar(191) default 'https://mysite.info'::character varying,
    stop_service_date       timestamp(0),
    msisdn_1_last_sms_sent  integer      default 1667701547                                         not null,
    msisdn_2_last_sms_sent  integer      default 1667701547                                         not null,
    is_sent_msisdn_1        boolean      default true                                               not null,
    is_sent_msisdn_2        boolean      default true                                               not null,
    sms_limit_msisdn_1      integer      default 5                                                  not null,
    sms_limit_msisdn_2      integer      default 5                                                  not null,
    first_sim_iccid         varchar(191),
    second_sim_iccid        varchar(191),
    signal_strength         varchar(191),
    delay_signal_strength   varchar(191) default '60'::character varying                            not null,
    sms_per_month_1         integer      default 0,
    sms_per_month_2         integer      default 0,
    signal_strength_sim_1   varchar(191),
    signal_strength_sim_2   varchar(191),
    signal_strength_wifi    varchar(191)
);

create index user_apps_connection_date_index
    on user_apps (connection_date);

create index user_apps_is_active_index
    on user_apps (is_active);

create index user_apps_is_passed_index
    on user_apps (is_passed);

CREATE TABLE



CREATE INDEX



CREATE INDEX



CREATE INDEX


-- auto-generated definition
create table sms
(
    id                   serial
        primary key,
    sender               varchar(191),
    msisdn_receiver      varchar(191),
    message              varchar(191),
    insertion_date       timestamp(0),
    to_device_date       timestamp(0),
    delivery_date        timestamp(0),
    success              integer,
    error                varchar(191),
    take                 boolean,
    created_at           timestamp(0),
    updated_at           timestamp(0),
    deleted_at           timestamp(0),
    task_id              integer,
    device_id            integer,
    status               varchar(20),
    sim                  varchar(191),
    country_code         varchar(191),
    priority             integer      default 1                        not null,
    user_app_id          integer
        constraint sms_user_app_id_foreign
            references user_apps
            on delete set null,
    sms_server_id        bigint,
    attempt              integer      default 0                        not null,
    is_queues            boolean      default false                    not null,
    type                 varchar(255) default 'sms'::character varying not null
        constraint sms_type_check
            check ((type)::text = ANY
                   ((ARRAY ['logs'::character varying, 'test'::character varying, 'sms'::character varying])::text[])),
    mcc_mnc              varchar(191),
    is_manual_delivery   boolean      default false                    not null,
    manual_delivery_time timestamp(0),
    sim_iccid            varchar(191),
    method               varchar(191)
);

create index sms_deleted_at_index
    on sms (deleted_at);

create index sms_user_app_id_index
    on sms (user_app_id);

create index sms_attempt_index
    on sms (attempt);

create index sms_status_index
    on sms (status);

create index sms_type_index
    on sms (type);

create index sms_country_code_index
    on sms (country_code);

create index sms_sim_iccid_index
    on sms (sim_iccid);

create index sms_insertion_date_index
    on sms (insertion_date);


CREATE TABLE



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX


insert into user_apps (unique_id, is_active, msisdn_1, msisdn_2, sms_per_day_1, sms_per_day_2, country_code, user_id, id, created_at, updated_at, firebase_token, ip, connection_date, description, date_of_reset_msisdn_1, date_of_reset_msisdn_2, version_of_package, is_suspended, is_passed, is_sent_email, msisdn_1_is_blocked, msisdn_2_is_blocked, block_sim_1_time, block_sim_2_time, unblock_msisdn_1_time, unblock_msisdn_2_time, msisdn_1_is_in_group, msisdn_2_is_in_group, msisdn_1_operator_group, msisdn_2_operator_group, disconnected_at, delay_sms_send, delay_is_connected, delay_is_keep_alive, is_connected, is_keep_alive, protocol_min, connection_type, url, stop_service_date, msisdn_1_last_sms_sent, msisdn_2_last_sms_sent, is_sent_msisdn_1, is_sent_msisdn_2, sms_limit_msisdn_1, sms_limit_msisdn_2, first_sim_iccid, second_sim_iccid, signal_strength, delay_signal_strength, sms_per_month_1, sms_per_month_2, signal_strength_sim_1, signal_strength_sim_2, signal_strength_wifi)
values  ('d1154bfd00b0d1f1', true, 'Namaste', 'Namaste', 1000, 1000, 'NP', 523, 1918, '2023-10-13 13:39:47', '2024-06-01 11:15:06', null, '2405:acc0:1207:5716:e806:32de:47c5:2655', '2024-05-25 23:37:15', null, '2024-04-22 18:21:59', '2024-04-21 18:02:51', '2.2.25', false, false, false, false, false, '2024-05-27 03:24:23', '2024-05-31 16:10:23', '2024-05-27 09:23:25', '2024-05-31 16:13:50', false, false, null, null, '2024-06-01 08:29:27', 3, 900, 120, true, true, '1.0', null, 'https://mysite.info', '2024-05-28 23:27:03', 1717233193, 1717232992, true, true, 5, 5, '8997701022333665771', '8997701022333665763', null, '60', 0, 0, '-71', '-97', '-1000'),
        ('bd6e32a4c1c29b22', true, 'One.al', 'One.al', 1000, 1000, 'AL', 66, 2227, '2024-03-07 08:54:16', '2024-06-01 11:17:43', null, '79.106.203.57', '2024-05-08 16:42:58', null, '2024-03-25 15:51:18', '2024-03-25 15:51:20', '2.2.12', false, false, false, false, true, '2024-05-29 01:02:39', '2024-05-26 00:40:13', '2024-05-29 08:40:51', '2024-05-16 16:17:53', false, false, null, null, '2024-06-01 08:56:14', 3, 900, 120, false, true, '1.0', 'Wi-Fi', 'https://mysite.info', '2024-05-08 16:27:58', 1717233176, 1716676807, true, true, 5, 5, '89355012205129459544', '89355012205129452366', '-35', '60', 0, 0, null, null, null),
        ('4412821353cf63e3', true, 'One.al', 'One.al', 1000, 1000, 'AL', 66, 2228, '2024-03-08 05:07:33', '2024-06-01 11:17:15', null, '79.106.203.57', '2024-05-08 16:43:58', null, null, null, '2.2.12', false, false, false, false, true, '2024-05-31 09:59:42', '2024-05-08 17:51:28', '2024-05-31 11:05:41', '2024-04-08 16:36:27', false, false, null, null, '2024-05-29 22:53:21', 3, 900, 120, true, true, '1.0', 'Wi-Fi', 'https://mysite.info', '2024-04-25 17:04:00', 1717233345, 1715183482, true, true, 5, 5, '89355012207082908251', '89355012102166846834', '-31', '60', 0, 0, null, null, null),
        ('43d4126cd5be2624', true, 'One.al', 'One.al', 1000, 1000, 'AL', 66, 2237, '2024-03-12 07:55:59', '2024-06-01 11:17:56', null, '79.106.203.57', '2024-05-08 16:45:17', null, null, null, '2.2.12', false, false, false, false, true, '2024-03-20 16:22:38', '2024-03-20 16:26:54', '2024-05-25 12:06:14', null, false, false, null, null, '2024-06-01 08:57:50', 3, 900, 120, true, true, '1.0', 'Wi-Fi', 'https://mysite.info', '2024-05-08 16:12:44', 1717233470, 1710948407, true, true, 5, 5, '89355012207072042608', '89355012207082908244', '-30', '60', 0, 0, null, null, null),
        ('d03676da3fea8c58', true, 'MTN', 'MTN', 1000, 1000, 'AF', 1093, 2259, '2024-03-26 17:32:29', '2024-06-01 11:17:43', null, '103.115.14.14', '2024-04-03 07:02:56', null, null, null, '2.2.12', false, false, false, false, false, '2024-05-30 20:19:04', '2024-05-25 11:51:28', '2024-05-30 20:30:38', '2024-05-25 13:48:49', false, false, null, null, '2024-06-01 10:10:47', 3, 900, 120, false, true, '1.0', 'Wi-Fi', 'https://mysite.info', '2024-06-01 06:04:15', 1717233454, 1717233024, true, true, 5, 5, '8993200300568367981', '8993400416310193026', '-30', '60', 5000, 5000, null, null, null),
        ('010dcdcfe2719c39', true, 'Namaste', 'Namaste', 200, 200, 'NP', 705, 2353, '2024-05-22 10:57:31', '2024-06-01 11:15:02', null, '49.126.95.220', '2024-05-23 07:01:40', null, '2024-05-26 07:08:53', '2024-05-25 13:24:18', '2.2.22', false, false, false, false, false, null, null, '2024-05-26 07:08:53', '2024-05-25 13:24:18', false, false, null, null, '2024-06-01 10:59:34', 3, 900, 120, false, true, '1.0', null, 'https://mysite.info', '2024-05-31 05:33:06', 1717232850, 1717232845, true, true, 5, 5, '8997701062344050209', '8997701062344050191', null, '60', 5000, 5000, '-90', '-90', '-1000');

INSERT 0 6


INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579877, '', '+45675296242', 'eb 543277 UE', '2024-06-01 11:10:24', '2024-06-01 11:10:28', null, 0, null, null, '2024-06-01 11:10:24', '2024-06-01 11:10:50', null, 1, null, 'delivered', 'msisdn_2', 'AF', 1, 2259, 1717233030, 1, true, 'sms', '41240', true, '2024-06-01 11:10:50', '8993400416310193026', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579876, '', '+34875296242', 'دrJbA/XP1K+V', '2024-06-01 11:10:24', '2024-06-01 11:10:28', '2024-06-01 11:10:32', 1, null, null, '2024-06-01 11:10:24', '2024-06-01 11:10:32', null, 1, null, 'delivered', 'msisdn_2', 'AF', 1, 2259, 1717233030, 1, true, 'sms', '41240', false, null, '8993400416310193026', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579875, '', '+832192078809', 'SWfo 851706', '2024-06-01 11:10:10', '2024-06-01 11:10:13', '2024-06-01 11:10:19', 1, null, null, '2024-06-01 11:10:10', '2024-06-01 11:10:19', null, 1, null, 'delivered', 'msisdn_1', 'AL', 1, 2227, 1717233016, 1, true, 'sms', '27602', false, null, '89355012205129459544', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579873, '', '+8343767804889', 'CK 2300 KA', '2024-06-01 11:09:52', '2024-06-01 11:09:53', '2024-06-01 11:10:00', 1, null, null, '2024-06-01 11:09:52', '2024-06-01 11:10:00', null, 1, null, 'delivered', 'msisdn_2', 'NP', 1, 1918, 1717232998, 1, true, 'sms', '42901', false, null, '8997701022333665763', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579867, '', '+123473114505', '748824', '2024-06-01 11:09:11', '2024-06-01 11:09:13', '2024-06-01 11:09:17', 1, null, null, '2024-06-01 11:09:11', '2024-06-01 11:09:17', null, 1, null, 'delivered', 'msisdn_1', 'AL', 1, 2237, 1717232957, 1, true, 'sms', '27601', false, null, '89355012207072042608', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579862, '', '+123497224823', 'WcBO 306076', '2024-06-01 11:08:25', '2024-06-01 11:08:28', '2024-06-01 11:08:33', 1, null, null, '2024-06-01 11:08:25', '2024-06-01 11:08:33', null, 1, null, 'delivered', 'msisdn_1', 'AL', 1, 2228, 1717232911, 1, true, 'sms', '27602', false, null, '89355012207082908251', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579857, '', '+123493501022', 'iYlN 289637', '2024-06-01 11:07:37', '2024-06-01 11:07:38', '2024-06-01 11:07:42', 1, null, null, '2024-06-01 11:07:37', '2024-06-01 11:07:42', null, 1, null, 'delivered', 'msisdn_1', 'AL', 1, 2227, 1717232863, 1, true, 'sms', '27602', false, null, '89355012205129459544', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579854, '', '+4563862372418', 'Li 972000 pP', '2024-06-01 11:07:30', '2024-06-01 11:07:33', null, 0, null, null, '2024-06-01 11:07:30', '2024-06-01 11:08:01', null, 1, null, 'delivered', 'msisdn_1', 'NP', 1, 2353, 1717232856, 1, true, 'sms', '42901', true, '2024-06-01 11:08:01', '8997701062344050209', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579853, '', '+3784840837003', 'fc 7922 Sp', '2024-06-01 11:07:25', '2024-06-01 11:07:28', null, 0, null, null, '2024-06-01 11:07:25', '2024-06-01 11:08:02', null, 1, null, 'delivered', 'msisdn_2', 'NP', 1, 2353, 1717232851, 1, true, 'sms', '42901', true, '2024-06-01 11:08:02', '8997701062344050191', 'SENT_SMS');
INSERT INTO sms (id, sender, msisdn_receiver, message, insertion_date, to_device_date, delivery_date, success, error, take, created_at, updated_at, deleted_at, task_id, device_id, status, sim, country_code, priority, user_app_id, sms_server_id, attempt, is_queues, type, mcc_mnc, is_manual_delivery, manual_delivery_time, sim_iccid, method) VALUES (13579848, '', '+763495182675', 'qIKmG 128055', '2024-06-01 11:06:58', '2024-06-01 11:07:03', '2024-06-01 11:07:07', 1, null, null, '2024-06-01 11:06:58', '2024-06-01 11:07:07', null, 1, null, 'delivered', 'msisdn_1', 'AL', 1, 2237, 1717232825, 1, true, 'sms', '27602', false, null, '89355012207072042608', 'SENT_SMS');

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