RE: wrong rows and cost estimation when generic plan

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

 



Hi,

   It's a prepared sql statement on a non-partitioned table , 16millions tuples  and multiple indexes on this table. pk_xxxxx  primary key (aid,bid,btype) all 3 cols are bigint datatype, there is another index idx_xxxxx(starttime,endtime) , both cols are "timestamp(0) without time zone". 

   the data distribution is skewed, not even. with first 5 times execution custom_plan, optimizer choose primary key, but when it start building generic plan and choose another index idx_xxxx, obviously generic plan make significant different rows and cost estimation. 

    below is the sql , sensitive info got masked here (tablename, columnname) .

 

   --with custom_plan

  Update on xxxxx  (cost=0.56..8.60 rows=1 width=2923) (actual time=0.030..0.031 rows=0 loops=1)

   Buffers: shared hit=4

   ->  Index Scan using pk_xxxxx on xxxxxxx  (cost=0.56..8.60 rows=1 width=2923) (actual time=0.028..0.028 rows=0 loops=1)

         Index Cond: ((aid = '14654072'::bigint) AND (bid = '243379969878556159'::bigint) AND (btype = '0'::bigint))

         Filter: ((password IS NULL) AND ...) AND (starttime = '2071-12-31 00:00:00'::timestamp without time zone) AND (endtime = '2072-01-01 00:00:00'::timestamp without time zone) AND (opentime = '2022-11-07 09:

40:26'::timestamp without time zone)

         Buffers: shared hit=4

 Planning Time: 1.575 ms

 Execution Time: 0.123 ms

 

 --after 5 times execution, it start to build generic plan and thought generic plan cost=0.44..8.48 that less than the customer plan ,so it choose generic plan for following sql executions,   

   Update on xxxxx  (cost=0.44..8.48 rows=1 width=2923) (actual time=8136.243..8136.245 rows=0 loops=1)

   Buffers: shared hit=1284549

   ->  Index Scan using idx_xxxxx_time on xxxxx  (cost=0.44..8.48 rows=1 width=2923) (actual time=8136.242..8136.242 rows=0 loops=1)

         Index Cond: ((starttime = $7) AND (endtime = $8))

         Filter: ((password IS NULL) AND ...(aid = $4) AND (bid = $5) AND (btype = $6) AND...

         Rows Removed by Filter: 5534630 

         Buffers: shared hit=1284549

 Planning Time: 0.754 ms

 Execution Time: 8136.302 ms

 

    as a workaround, I remove "starttime" and "endtime" stats tuple from  pg_statistic, and optimizer use a DEFAULT value with NULL stats tuple  so that index_path cost > the primary key index_path cost, following eqsel function logic,  postgres/selfuncs.c at REL_13_STABLE · postgres/postgres · GitHub

    optimzer is very complicated, could you direct me how optimizer to do selectivity estimation when building generic plan, for this case? for custom_plan, optimizer knows boundparams values, but when generic_plan, planner() use boundparams=NULL,  it try to calculate average value based on mcv list of the index attributes (starttime,endtime)  ?

    please check attached about sql details and pg_stats tuple for the index attributes.

 

Thanks,

 

James


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

  Powered by Linux