On Fri, 2 Dec 2022 at 12:21, Justin Pryzby <pry...@telsasoft.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
