title: PostgreSQL and MongoDB WHERE IN condtions
body: <p>Sorry for my bad English. I have a table in postgres like:</p>
  <pre><code>CREATE TABLE report
  (
      id     serial primary key,
      sensor_id integer,
      datetime timestamp,
      ...(many other fields)
  );
  </code></pre>
  <p>Collection in mongo stores the same data.
  Index in Postgres (I created all of them and separatly, but result is the same) :</p>
  <pre><code>create index report_datetime on report (datetime desc);
  create index report_sensor_datetime on report (datetime desc, sensor_id);
  create index report_sensor_datetime_1 on report (sensor_id, datetime desc);
  </code></pre>
  <p>Index in Mongo:</p>
  <pre><code>...createIndex({&quot;sensor_id&quot; : 1, &quot;datetime&quot; : -1})
  </code></pre>
  <p>Table\collection has &gt; 100 millions records.</p>
  <p>So, my question is why query in postgres like:</p>
  <pre><code>select *
  from report
  where sensor_id in (10234, 10236, 10250, 10251, 10253, 10255, 10262, 10263, 10264, 10265, 10267)
  order by datetime desc
  limit 100;
  </code></pre>
  <p>perfoms for many seconds (see explain below), but similar request in mongo:</p>
  <pre><code>db.getCollection('report').find({sensor_id: {$in: [
      ObjectId(&quot;5f1833aa44be73ead6de88c9&quot;),
      ObjectId(&quot;5f1833ad44be73ead6de8977&quot;),
      ObjectId(&quot;5f18a0867c9195aa25ac0bb0&quot;),
      ObjectId(&quot;5f18a0867c9195aa25ac0bb5&quot;),
      ObjectId(&quot;5f18a0a47c9195aa25ac1241&quot;),
      ObjectId(&quot;5f1927b87c9195aa25b5203c&quot;),
      ObjectId(&quot;5f1a5f617c9195aa25e2d4df&quot;),
      ObjectId(&quot;5f1a5f647c9195aa25e2db3b&quot;),
      ObjectId(&quot;5f1a5f647c9195aa25e2db42&quot;),
      ObjectId(&quot;5f1a5f647c9195aa25e2db59&quot;),
      ObjectId(&quot;5f1a69027c9195aa25e4f84d&quot;)
  ]}}).sort({datetime:-1}).limit(100)
  </code></pre>
  <p>makes result for milliseconds?</p>
  <p><strong>How can I improve Postgres query or indexes for the same result? I don't belive that postgres can't do this query fast like mongo.</strong></p>
  <p>Mongo explain:</p>
  <pre><code>    db.getCollection('report').find({sensor_id: {$in: [
          ObjectId(&quot;5f1833aa44be73ead6de88c9&quot;),
          ObjectId(&quot;5f1833ad44be73ead6de8977&quot;),
          ObjectId(&quot;5f18a0867c9195aa25ac0bb0&quot;),
          ObjectId(&quot;5f18a0867c9195aa25ac0bb5&quot;),
          ObjectId(&quot;5f18a0a47c9195aa25ac1241&quot;),
          ObjectId(&quot;5f1927b87c9195aa25b5203c&quot;),
          ObjectId(&quot;5f1a5f617c9195aa25e2d4df&quot;),
          ObjectId(&quot;5f1a5f647c9195aa25e2db3b&quot;),
          ObjectId(&quot;5f1a5f647c9195aa25e2db42&quot;),
          ObjectId(&quot;5f1a5f647c9195aa25e2db59&quot;),
          ObjectId(&quot;5f1a69027c9195aa25e4f84d&quot;)
      ]}}).sort({datetime:-1}).limit(100).explain()
  {
      &quot;queryPlanner&quot; : {
          &quot;plannerVersion&quot; : 1,
          &quot;namespace&quot; : &quot;patrol.report&quot;,
          &quot;indexFilterSet&quot; : false,
          &quot;parsedQuery&quot; : {
              &quot;sensor_id&quot; : {
                  &quot;$in&quot; : [ 
                      ObjectId(&quot;5f1833aa44be73ead6de88c9&quot;), 
                      ObjectId(&quot;5f1833ad44be73ead6de8977&quot;), 
                      ObjectId(&quot;5f18a0867c9195aa25ac0bb0&quot;), 
                      ObjectId(&quot;5f18a0867c9195aa25ac0bb5&quot;), 
                      ObjectId(&quot;5f18a0a47c9195aa25ac1241&quot;), 
                      ObjectId(&quot;5f1927b87c9195aa25b5203c&quot;), 
                      ObjectId(&quot;5f1a5f617c9195aa25e2d4df&quot;), 
                      ObjectId(&quot;5f1a5f647c9195aa25e2db3b&quot;), 
                      ObjectId(&quot;5f1a5f647c9195aa25e2db42&quot;), 
                      ObjectId(&quot;5f1a5f647c9195aa25e2db59&quot;), 
                      ObjectId(&quot;5f1a69027c9195aa25e4f84d&quot;)
                  ]
              }
          },
          &quot;queryHash&quot; : &quot;C4968B0D&quot;,
          &quot;planCacheKey&quot; : &quot;58DC1ACB&quot;,
          &quot;winningPlan&quot; : {
              &quot;stage&quot; : &quot;LIMIT&quot;,
              &quot;limitAmount&quot; : 100,
              &quot;inputStage&quot; : {
                  &quot;stage&quot; : &quot;FETCH&quot;,
                  &quot;inputStage&quot; : {
                      &quot;stage&quot; : &quot;SORT_MERGE&quot;,
                      &quot;sortPattern&quot; : {
                          &quot;datetime&quot; : -1.0
                      },
                      &quot;inputStages&quot; : [ 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1833aa44be73ead6de88c9'), ObjectId('5f1833aa44be73ead6de88c9')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1833ad44be73ead6de8977'), ObjectId('5f1833ad44be73ead6de8977')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f18a0867c9195aa25ac0bb0'), ObjectId('5f18a0867c9195aa25ac0bb0')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f18a0867c9195aa25ac0bb5'), ObjectId('5f18a0867c9195aa25ac0bb5')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f18a0a47c9195aa25ac1241'), ObjectId('5f18a0a47c9195aa25ac1241')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1927b87c9195aa25b5203c'), ObjectId('5f1927b87c9195aa25b5203c')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1a5f617c9195aa25e2d4df'), ObjectId('5f1a5f617c9195aa25e2d4df')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1a5f647c9195aa25e2db3b'), ObjectId('5f1a5f647c9195aa25e2db3b')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1a5f647c9195aa25e2db42'), ObjectId('5f1a5f647c9195aa25e2db42')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1a5f647c9195aa25e2db59'), ObjectId('5f1a5f647c9195aa25e2db59')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }, 
                          {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : 1,
                                  &quot;datetime&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_datetime&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : [],
                                  &quot;datetime&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1a69027c9195aa25e4f84d'), ObjectId('5f1a69027c9195aa25e4f84d')]&quot;
                                  ],
                                  &quot;datetime&quot; : [ 
                                      &quot;[MaxKey, MinKey]&quot;
                                  ]
                              }
                          }
                      ]
                  }
              }
          },
          &quot;rejectedPlans&quot; : [ 
              {
                  &quot;stage&quot; : &quot;SORT&quot;,
                  &quot;sortPattern&quot; : {
                      &quot;datetime&quot; : -1.0
                  },
                  &quot;limitAmount&quot; : 100,
                  &quot;inputStage&quot; : {
                      &quot;stage&quot; : &quot;SORT_KEY_GENERATOR&quot;,
                      &quot;inputStage&quot; : {
                          &quot;stage&quot; : &quot;FETCH&quot;,
                          &quot;inputStage&quot; : {
                              &quot;stage&quot; : &quot;IXSCAN&quot;,
                              &quot;keyPattern&quot; : {
                                  &quot;sensor_id&quot; : -1
                              },
                              &quot;indexName&quot; : &quot;sensor_id&quot;,
                              &quot;isMultiKey&quot; : false,
                              &quot;multiKeyPaths&quot; : {
                                  &quot;sensor_id&quot; : []
                              },
                              &quot;isUnique&quot; : false,
                              &quot;isSparse&quot; : false,
                              &quot;isPartial&quot; : false,
                              &quot;indexVersion&quot; : 2,
                              &quot;direction&quot; : &quot;forward&quot;,
                              &quot;indexBounds&quot; : {
                                  &quot;sensor_id&quot; : [ 
                                      &quot;[ObjectId('5f1a69027c9195aa25e4f84d'), ObjectId('5f1a69027c9195aa25e4f84d')]&quot;, 
                                      &quot;[ObjectId('5f1a5f647c9195aa25e2db59'), ObjectId('5f1a5f647c9195aa25e2db59')]&quot;, 
                                      &quot;[ObjectId('5f1a5f647c9195aa25e2db42'), ObjectId('5f1a5f647c9195aa25e2db42')]&quot;, 
                                      &quot;[ObjectId('5f1a5f647c9195aa25e2db3b'), ObjectId('5f1a5f647c9195aa25e2db3b')]&quot;, 
                                      &quot;[ObjectId('5f1a5f617c9195aa25e2d4df'), ObjectId('5f1a5f617c9195aa25e2d4df')]&quot;, 
                                      &quot;[ObjectId('5f1927b87c9195aa25b5203c'), ObjectId('5f1927b87c9195aa25b5203c')]&quot;, 
                                      &quot;[ObjectId('5f18a0a47c9195aa25ac1241'), ObjectId('5f18a0a47c9195aa25ac1241')]&quot;, 
                                      &quot;[ObjectId('5f18a0867c9195aa25ac0bb5'), ObjectId('5f18a0867c9195aa25ac0bb5')]&quot;, 
                                      &quot;[ObjectId('5f18a0867c9195aa25ac0bb0'), ObjectId('5f18a0867c9195aa25ac0bb0')]&quot;, 
                                      &quot;[ObjectId('5f1833ad44be73ead6de8977'), ObjectId('5f1833ad44be73ead6de8977')]&quot;, 
                                      &quot;[ObjectId('5f1833aa44be73ead6de88c9'), ObjectId('5f1833aa44be73ead6de88c9')]&quot;
                                  ]
                              }
                          }
                      }
                  }
              }
          ]
      },
      &quot;serverInfo&quot; : {
          &quot;host&quot; : &quot;mongo&quot;,
          &quot;port&quot; : 27017,
          &quot;version&quot; : &quot;4.2.12&quot;,
          &quot;gitVersion&quot; : &quot;5593fd8e33b60c75802edab304e23998fa0ce8a5&quot;
      },
      &quot;ok&quot; : 1.0
  }
  </code></pre>
  <p>Postgres explain:</p>
  <pre><code>explain (analyze, buffers)
  select *
  from report
  where sensor_id in (10234, 10236, 10250, 10251, 10253, 10255, 10262, 10263, 10264, 10265, 10267)
  order by datetime desc
  limit 100;
  
  Limit  (cost=0.57..3984.27 rows=100 width=312) (actual time=147481.199..154733.025 rows=100 loops=1)
    Buffers: shared hit=16330933 read=631496
    -&gt;  Index Scan using report_datetime on report  (cost=0.57..69625427.65 rows=1747758 width=312) (actual time=147481.195..154732.770 rows=100 loops=1)
          Filter: (sensor_id = ANY ('{10234,10236,10250,10251,10253,10255,10262,10263,10264,10265,10267}'::integer[]))
          Rows Removed by Filter: 17276154
          Buffers: shared hit=16330933 read=631496
  Planning:
    Buffers: shared hit=128 read=3
  Planning Time: 14.662 ms
  Execution Time: 154733.156 ms
  </code></pre>
  <p>Additional question. When I run Postgres query with the same parameters it performs fast, but any add\remove\replace sensor_id will again slow. Mongo perfoms fast response on any params. For example, second Postgres query (but is also too slow):</p>
  <pre><code>explain (analyze, buffers)
  select *
  from report
  where sensor_id in (10234, 10236, 10250, 10251, 10253, 10255, 10262, 10263, 10264, 10265, 10267)
  order by datetime desc
  limit 100;
  
  Limit  (cost=0.57..3984.27 rows=100 width=312) (actual time=22828.487..22949.028 rows=100 loops=1)
    Buffers: shared hit=16932070 read=30359
    -&gt;  Index Scan using report_datetime on report  (cost=0.57..69625427.65 rows=1747758 width=312) (actual time=22828.483..22948.984 rows=100 loops=1)
          Filter: (sensor_id = ANY ('{10234,10236,10250,10251,10253,10255,10262,10263,10264,10265,10267}'::integer[]))
          Rows Removed by Filter: 17276154
          Buffers: shared hit=16932070 read=30359
  Planning Time: 0.253 ms
  Execution Time: 22949.084 ms
  </code></pre>
  <p>Postgres conf (Mongo has default conf):</p>
  <pre><code>max_connections = 200
  shared_buffers = 8GB
  effective_cache_size = 24GB
  maintenance_work_mem = 2GB
  checkpoint_completion_target = 0.7
  wal_buffers = 16MB
  default_statistics_target = 100
  random_page_cost = 4
  effective_io_concurrency = 2
  work_mem = 10485kB
  min_wal_size = 1GB
  max_wal_size = 4GB
  max_worker_processes = 8
  max_parallel_workers_per_gather = 4
  max_parallel_workers = 8
  max_parallel_maintenance_workers = 4
  </code></pre>