Re: Odd Choice of seq scan

On Fri, 2 Dec 2022 at 12:21, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> Could you show explain analyze ?
>
> Show the size of the table and its indexes
> And GUC settings
> And the "statistics" here:
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
> Maybe on both a well-behaving instance and a badly-beving instance.
>
>
Analyzes below, but they are both "badly" behaved and the plans. The index
scan is presumably a marginally better option when the magic that allows
"index only" lines up, but it's still a scan of the whole index rather than
an index lookup. Both plans fetch "everything" and then filter out all but
the 0 to 2 rows that match.

In my head the stats should be simple, as
1) The
"orderstotrans_transid_key" UNIQUE, btree (transid)
means the subquery can return at most one order_id when I look up by
trans_id (and the query plan does seem to know that, ie says rows=1)
2) The other OR'd clause is exactly one order_id.

So the worst case scenario is effectively the same as:
select orders.orderid FROM orders WHERE (orders.orderid IN
('546111','436345353'));
which would be:
====
Index Only Scan using orders_pkey on orders (cost=0.57..13.17 rows=2
width=8) (actual time=0.038..0.039 rows=1 loops=1)
Index Cond: (orderid = ANY ('{546111,436345353}'::bigint[]))
====
ie "Index Cond" rather than "filter"

Anyway, maybe that insight is more naturally obvious to a human than
something the planner can determine cheaply and easily.

The alternate "union" phrasing of the query works and as Ronuk and Tom said
in other replies (thanks) seems to be the way to go and for now at least I
just need to remember that ORs like this don't help the planner and should
be avoided.

Thanks all.

====
explain analyze
select orders.orderid FROM orders WHERE (orders.orderid IN ('546111') OR
orders.orderid IN (select orderid FROM orderstotrans WHERE (transid IN
('546111'))));

=====
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on orders (cost=8.45..486499.59 rows=4304805 width=8) (actual
time=9623.981..20796.568 rows=1 loops=1)
Filter: ((orderid = '546111'::bigint) OR (hashed SubPlan 1))
Rows Removed by Filter: 8615097
SubPlan 1
-> Index Scan using orderstotrans_transid_key on orderstotrans
(cost=0.43..8.45 rows=1 width=8) (actual time=1.105..1.105 rows=0 loops=1)
Index Cond: (transid = '546111'::bigint)
Planning Time: 0.199 ms
Execution Time: 20796.613 ms
====

QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using orders_pkey on orders (cost=9.16..4070119.84
rows=64770768 width=8) (actual time=21011.157..21011.158 rows=0 loops=1)
Filter: ((orderid = '546111'::bigint) OR (hashed SubPlan 1))
Rows Removed by Filter: 130888763
Heap Fetches: 3171118
SubPlan 1
-> Index Scan using orderstotrans_transid_key on orderstotrans
(cost=0.57..8.59 rows=1 width=8) (actual time=1.113..1.113 rows=0 loops=1)
Index Cond: (transid = '546111'::bigint)
Planning Time: 0.875 ms
Execution Time: 21011.224 ms