title: Different Performances On Postgresql Materialized View For Similar Queries
body: <p>I have a table which has 3 columns; id(serial), jsonbData(jsonb), date(timestamp) with 3.200.000 rows. I created a materialized view from this table which has exactly the same columns as table. Both materialized view and table has btree indexes on jsonb keys and date column.</p>
  <p>I execute this query on both table and view. (endDate and startDate are static):</p>
  <pre><code>select * from table/matView where date &lt;= endDate and date &gt;= startDate and jsonbData -&gt;&gt; 'name' = x 
  </code></pre>
  <p>The performances of materialized view and table are nearly same but whenever I change query so it selects a randomDate as endDate and randomDate - 180 days as startDate, execution time of table doesn't change but materialized view takes nearly 4 times longer than before.
  I couldn't figure out what is the reason of performance decrease of materialized view. The only difference between table and materialized view is id column on table is primary key and has index on it.</p>
  <p>Table:</p>
  <pre><code>CREATE TABLE app_fraud (
      app_data_id serial4 NOT NULL,
      app_content jsonb NULL,
      created_at timestamp NULL,
      CONSTRAINT app_fraud_pkey PRIMARY KEY (app_data_id)
  );
  
  CREATE INDEX app_fraud_created_at_idx ON app_fraud USING btree (created_at);
  CREATE INDEX app_fraud_expr_idx ON app_fraud USING btree (((app_content -&gt;&gt; 'ma_TCKN'::text)));
  </code></pre>
  <p>Materialized View:</p>
  <pre><code>create materialized view app_fraud_mat_view as select app_data_id, app_content, created_at from app_fraud;
  
  CREATE INDEX app_fraud_created_at_idx22 ON app_fraud_mat_view USING btree (created_at);
  CREATE INDEX app_fraud_expr_idx22 ON app_fraud_mat_view USING btree (((app_content -&gt;&gt; 'ma_TCKN'::text)));
  </code></pre>
  <p>Explain Analyze Query For Table:</p>
  <pre><code>explain(analyze,buffers)
  select * from app_fraud where created_at &lt;= '2020-12-19' and created_at &gt;= '2019-01-01' and app_content -&gt;&gt; 'ma_TCKN' = '1611738921030';
  </code></pre>
  <p><a href="https://i.stack.imgur.com/04Rdy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/04Rdy.png" alt="enter image description here" /></a></p>
  <p>Explain Analyze Query For Materialized View:</p>
  <pre><code>explain(analyze,buffers)
  select * from app_fraud_mat_view where created_at &lt;= '2020-12-19' and created_at &gt;= '2019-01-01' and app_content -&gt;&gt; 'ma_TCKN' = '1611738921030';
  </code></pre>
  <p><a href="https://i.stack.imgur.com/XFgIM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XFgIM.png" alt="enter image description here" /></a></p>