Re: Odd Choice of seq scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux