Trying out CREATE RULE ... ON SELECT

gistfile1.txt

-- ref https://dba.stackexchange.com/questions/229725/how-to-utilize-an-index-when-filtering-a-view?noredirect=1#comment453204_229725
-- the comment from Akina seems to indicate that creating the view through this syntax should somehow
-- affect the result? Very unsure if I understand the suggestion correctly.


Drop table form2;
CREATE TABLE form2 (
    id INT,
    encounter_id INT             NOT NULL,
    type         TEXT                                      NOT NULL,
  archived  BOOLEAN                   NOT NULL DEFAULT FALSE

);
commit;

CREATE RULE "_RETURN" AS ON SELECT TO form2 DO INSTEAD
  SELECT DISTINCT ON (f.id)
    f.id,
    f.encounter_id,
    f.type,
    fd.archived,
  FROM _form f
    JOIN _form_details fd ON (f.id = fd.form_id)
  ORDER BY f.id, fd.id DESC;

explain analyze
select * from form2 where encounter_id= 23728 and type = 'vitals';

The result is none the less the same :(
Subquery Scan on form2  (cost=0.57..3439.07 rows=1 width=622) (actual time=8.126..8.126 rows=0 loops=1)
  Filter: ((form2.encounter_id = 23728) AND (form2.type = 'vitals'::text))
  Rows Removed by Filter: 12000
  ->  Unique  (cost=0.57..3259.07 rows=12000 width=626) (actual time=0.008..7.584 rows=12000 loops=1)
        ->  Merge Join  (cost=0.57..3229.07 rows=12000 width=626) (actual time=0.007..5.467 rows=12000 loops=1)
              Merge Cond: (fd.form_id = f.id)
              ->  Index Scan using _idx_form_details on _form_details fd  (cost=0.29..2636.78 rows=12000 width=603) (actual time=0.003..1.968 rows=12000 loops=1)
              ->  Index Scan using pk_form on _form f  (cost=0.29..412.29 rows=12000 width=27) (actual time=0.002..1.163 rows=12000 loops=1)
Planning time: 0.180 ms
Execution time: 8.153 ms