Why does PostgreSQL use a sub-optimal execution plan for a simple query?

Question

PostgreSQL

I'm getting acquainted with PostgreSQL 9.5 and already many "simple" moments start to confuse me. Here is one of them. I create a params
table in the database :

CREATE TABLE params(
counter_id integer NOT NULL, 
"time" bigint NOT NULL, 
value integer NOT NULL, 
param integer NOT NULL, 
param_id bigint NOT NULL, 
CONSTRAINT param_id 
PRIMARY KEY (param_id));

I create an index for the params table :

CREATE INDEX counter_id_time ON params (counter_id, time);

I add 320 million records to the params table for experiments.
Then I run the following queries:

SELECT MAX(time) FROM params WHERE counter_id = 6001; -- 0,031 с

SELECT MAX(time) FROM params WHERE counter_id = 15998; -- 0,016 с

Then I execute the following query:

SELECT MAX(time) FROM params WHERE counter_id = 6001 OR counter_id = 15998; -- 139 с !??

Why does PostgreSQL choose such a thorny option for such a "simple" query?
Here is the plan for executing the query that the database issues:

"Aggregate  (cost=140975.88..140975.89 rows=1 width=8)"
"  ->  Bitmap Heap Scan on params  (cost=919.98..140878.74 rows=38853 width=8)"
"        Recheck Cond: ((counter_id = 6001) OR (counter_id = 15998))"
"        ->  BitmapOr  (cost=919.98..919.98 rows=38854 width=0)"
"              ->  Bitmap Index Scan on counter_id_time  (cost=0.00..450.27 rows=19427 width=0)"
"                    Index Cond: (counter_id = 6001)"
"              ->  Bitmap Index Scan on counter_id_time  (cost=0.00..450.27 rows=19427 width=0)"
"                    Index Cond: (counter_id = 15998)"

I had to "chew" the PostgreSQL query to this:

SELECT MAX(m) FROM (SELECT MAX(time) m FROM params WHERE counter_id = 6001 UNION SELECT MAX(time) m FROM params WHERE counter_id = 15998) p; -- 0,016 c

Here is the plan for executing this query that the database issues:

"Unique  (cost=9.23..9.24 rows=2 width=0)"
"  ->  Sort  (cost=9.23..9.24 rows=2 width=0)"
"        Sort Key: ($1)"
"        ->  Append  (cost=4.59..9.22 rows=2 width=0)"
"              ->  Result  (cost=4.59..4.60 rows=1 width=0)"
"                    InitPlan 2 (returns $1)"
"                      ->  Limit  (cost=0.57..4.59 rows=1 width=8)"
"                            ->  Index Only Scan Backward using counter_id_time on params params_1  (cost=0.57..78084.90 rows=19427 width=8)"
"                                  Index Cond: ((counter_id = 6001) AND ("time" IS NOT NULL))"
"              ->  Result  (cost=4.59..4.60 rows=1 width=0)"
"                    InitPlan 1 (returns $0)"
"                      ->  Limit  (cost=0.57..4.59 rows=1 width=8)"
"                            ->  Index Only Scan Backward using counter_id_time on params  (cost=0.57..78084.90 rows=19427 width=8)"
"                                  Index Cond: ((counter_id = 15998) AND ("time" IS NOT NULL))"

For example, in MySQL 5.7.10 and MS SQL 2014 SP1, a problematic query for PostgreSQL is not a problematic one.
Why am I doing this: when I need to make a selection by several counter_id , the number of which will be limited by the condition, PostgreSQL will still try to make a selection by all counter_id , but only at the end will weed out unnecessary values. Using UNIOIN is certainly an option, but I find it suboptimal in most cases.
What can be done to make PostgreSQL process such queries "correctly"?

Answer 1

Usually do counter_id in (6001, 15998) and not crowd or.

Aggregate (cost=18.52..18.53 rows=1 width=8) (actual time=1.586..1.586 rows=1 loops=1)
-> Index Only Scan using params_counter_id_time_idx on params (cost=0.56..18.51 rows=4 width =8) (actual time=0.769..1.566 rows=12 loops=1)
Index Cond: (counter_id = ANY ('{6001,15998}'::bigint[]))
Heap Fetches: 2
Planning time: 50.191 ms
Execution time: 1.933ms

There is an order of magnitude less data here, it's just a virtual machine and pg still 9.4
or two minutes of execution also does not give:

Aggregate (cost=25.20..25.21 rows=1 width=8) (actual time=38.797..38.797 rows=1 loops=1)
-> Bitmap Heap Scan on params (cost=9.16..25.19 rows=4 width=8 ) (actual time=0.362..38.749 rows=12 loops=1)
Recheck Cond: ((counter_id = 6001) OR (counter_id = 15998))
Heap Blocks: exact=12
-> BitmapOr (cost=9.16..9.16 rows= 4 width=0) (actual time=0.042..0.042 rows=0 loops=1)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.031..0.031 rows =6 loops=1)
Index Cond: (counter_id = 6001)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.009..0.009 rows=6 loops=1)
Index Cond: (counter_id = 15998)
Planning time: 0.247 ms
Execution time: 39.408 ms

Try doing analyze params;
Maybe the scheduler has old statistics.