|
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 |
Postgres v13, on Redhat8 RHEL8.4
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,
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.
SQL text:
UPDATE "xxxx"."xxxxx" set "bid" = $1::numeric::int8,"opentime" = $2,"lastmodifiedtime" = $3
where "aid" = $4::numeric::int8 AND "bid" = $5::numeric::int8 AND "btype" = $6::numeric::int8
AND "password" IS NULL AND "starttime" = $7 AND "endtime" = $8 AND ....
--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 exeution, it start to build generic plan
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
index_stats from pg_class
relname | relpages | reltuples | relallvisible | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relmin
mxid
-----------------------------+----------+---------------+---------------+----------------+--------------+----------------+------------+--------------+-------
-----
idx_xxxxx_time | 23157 | 2.1920316e+07 | 0 | t | n | f | 0 | 0 |
0
pk_xxxxx | 116004 | 2.1920316e+07 | 0 | t | n | f | 0 | 0 |
0
idx_xxxxx_time column stats pg_stats
schemaname | xxxxx
tablename | xxxxx
attname | starttime
inherited | f
null_frac | 0
avg_width | 8
n_distinct | 5778
most_common_vals | {"2071-12-31 00:00:00","2072-12-31 00:00:00","2070-12-31 00:00:00","2071-12-31 06:00:00","2069-12-31 00:00:00","2071-12-30 23:00:00"
,"2070-12-30 23:00:00","2072-12-31 01:00:00","2070-12-31 12:00:00","2072-12-30 23:00:00","2071-12-31 12:00:00","2071-12-30 19:30:00","2072-12-31 12:00:00","2
071-12-31 01:00:00","2072-12-31 06:00:00","2071-12-30 17:00:00","2071-12-31 09:00:00","2068-12-31 00:00:00","2070-12-31 01:00:00","2071-12-30 22:00:00","2072
-12-30 17:00:00","2072-12-31 09:00:00","2072-12-30 22:00:00","2071-12-31 07:00:00","2022-11-03 09:00:00","2022-09-13 07:00:00","2022-09-22 08:00:00","2022-10
-05 08:00:00","2022-10-24 08:00:00","2022-10-27 07:00:00","2022-11-03 10:00:00","2071-12-31 04:00:00","2022-11-04 09:00:00","2072-12-30 19:30:00","2072-12-31
04:00:00","2021-07-20 12:00:00","2022-09-01 08:00:00","2022-09-07 08:00:00","2022-09-26 11:00:00","2022-09-29 08:00:00","2022-10-11 08:00:00","2022-10-14 09
:00:00","2022-10-25 08:30:00","2022-10-26 08:00:00","2022-11-07 13:00:00","2022-09-07 07:00:00","2022-09-08 09:00:00","2022-09-27 07:00:00","2022-10-21 08:00
:00","2022-10-28 08:00:00","2022-11-03 08:00:00","2022-11-04 08:00:00","2070-12-30 22:00:00","2021-09-01 09:00:00","2022-08-11 09:00:00","2022-08-31 08:00:00
","2022-09-02 09:00:00","2022-09-13 08:00:00","2022-09-20 13:00:00","2022-09-21 12:00:00","2022-09-22 12:00:00","2022-10-10 12:00:00","2022-10-19 11:00:00","
2022-10-27 08:00:00","2022-11-08 09:00:00","2072-12-31 07:00:00","2021-07-19 12:00:00","2022-08-10 11:00:00","2022-08-10 12:00:00","2022-08-16 08:00:00","202
2-08-25 07:00:00","2022-08-25 08:00:00","2022-09-05 09:00:00","2022-09-09 12:00:00","2022-09-14 12:00:00","2022-09-20 09:00:00","2022-09-30 08:00:00","2022-1
0-10 08:00:00","2022-10-12 07:00:00","2022-10-12 09:00:00","2022-10-13 08:00:00","2022-10-18 12:00:00","2022-10-25 09:00:00","2022-10-26 07:30:00","2022-10-2
8 11:00:00","2069-12-30 23:00:00","2070-12-31 11:00:00","2021-06-22 07:00:00","2021-08-27 09:00:00","2021-09-02 13:00:00","2022-08-09 09:00:00","2022-08-11 0
8:00:00","2022-08-11 11:00:00","2022-08-16 12:00:00","2022-08-23 08:00:00","2022-08-26 08:00:00","2022-08-29 09:00:00","2022-08-31 07:30:00","2022-09-02 07:0
0:00","2022-09-07 13:00:00"}
most_common_freqs | {0.26266667,0.21486667,0.092766665,0.009333333,0.008,0.0045333332,0.0032,0.0030666667,0.0029333334,0.0027,0.0025,0.0018,0.0017666667
,0.0014666667,0.0014666667,0.0012,0.0011666666,0.0010666667,0.0010333334,0.001,0.0009,0.00076666666,0.0007,0.00066666666,0.0006,0.00053333334,0.0005,0.0005,0
.0005,0.0005,0.0005,0.0005,0.00046666668,0.00046666668,0.00046666668,0.00043333333,0.00043333333,0.00043333333,0.00043333333,0.00043333333,0.00043333333,0.00
043333333,0.00043333333,0.00043333333,0.00043333333,0.0004,0.0004,0.0004,0.0004,0.0004,0.0004,0.0004,0.0004,0.00036666667,0.00036666667,0.00036666667,0.00036
666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00033333333,0.00033333
333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333
,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.
0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003}
histogram_bounds | {"2008-05-22 04:59:00","2021-06-07 15:30:00","2021-06-09 11:00:00","2021-06-11 09:00:00","2021-06-15 08:30:00","2021-06-16 14:30:00"
,"2021-06-21 06:30:00","2021-06-23 06:00:00","2021-06-24 12:00:00","2021-06-28 11:05:00","2021-06-30 09:00:00","2021-07-02 07:30:00","2021-07-06 10:30:00","2
021-07-08 09:25:00","2021-07-12 11:00:00","2021-07-14 09:10:00","2021-07-16 11:30:00","2021-07-21 07:00:00","2021-07-23 11:30:00","2021-07-28 08:00:00","2021
-07-30 06:55:00","2021-08-03 11:00:00","2021-08-05 12:00:00","2021-08-09 16:00:00","2021-08-12 11:00:00","2021-08-17 08:00:00","2021-08-19 07:45:00","2021-08
-23 08:00:00","2021-08-25 08:30:00","2021-08-27 11:00:00","2021-08-31 11:30:00","2021-09-02 12:30:00","2021-09-07 07:00:00","2021-09-09 07:00:00","2021-09-13
10:00:00","2021-09-17 16:15:00","2021-10-25 11:35:00","2022-05-12 11:00:00","2022-08-02 11:00:00","2022-08-09 09:30:00","2022-08-11 06:00:00","2022-08-12 13
:30:09","2022-08-16 13:10:00","2022-08-18 08:30:00","2022-08-19 13:00:00","2022-08-23 07:00:00","2022-08-24 12:00:00","2022-08-26 06:30:00","2022-08-29 11:00
:00","2022-08-30 14:00:00","2022-09-01 09:30:00","2022-09-03 07:00:36","2022-09-06 08:30:00","2022-09-07 12:30:00","2022-09-08 16:00:00","2022-09-12 07:30:00
","2022-09-13 10:30:00","2022-09-14 11:20:00","2022-09-15 11:05:00","2022-09-16 12:00:00","2022-09-19 12:00:00","2022-09-20 20:17:49","2022-09-22 08:30:00","
2022-09-23 09:30:00","2022-09-26 13:00:00","2022-09-27 12:27:36","2022-09-28 14:15:00","2022-09-30 06:30:00","2022-10-03 15:00:00","2022-10-05 07:30:00","202
2-10-06 09:00:00","2022-10-07 12:30:00","2022-10-11 00:06:10","2022-10-12 07:15:05","2022-10-13 10:00:00","2022-10-14 11:00:00","2022-10-17 11:30:00","2022-1
0-18 12:15:00","2022-10-19 12:00:00","2022-10-20 13:00:00","2022-10-21 13:30:00","2022-10-24 14:00:00","2022-10-25 14:30:11","2022-10-27 05:00:00","2022-10-2
8 06:30:00","2022-10-31 11:30:00","2022-11-02 09:00:00","2022-11-03 08:30:00","2022-11-04 06:00:00","2022-11-07 07:00:00","2022-11-07 15:30:00","2022-11-09 0
8:00:00","2022-11-10 12:00:00","2022-11-14 14:00:00","2022-11-18 12:00:00","2022-11-30 11:00:00","2022-12-27 07:00:00","2069-12-31 12:00:00","2070-12-31 07:0
0:00","2071-12-31 02:00:00","2072-12-31 13:00:00"}
correlation | 0.284717
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
schemaname | xxxxx
tablename | xxxxx
attname | endtime
inherited | f
null_frac | 0
avg_width | 8
n_distinct | 6606
most_common_vals | {"2072-01-01 00:00:00","2073-01-01 00:00:00","2071-01-01 00:00:00","2071-12-31 00:50:00","2071-12-31 06:50:00","2070-01-01 00:00:00"
,"2072-12-31 00:40:00","2071-01-01 12:00:00","2073-01-01 01:00:00","2072-01-01 12:00:00","2072-12-31 23:00:00","2071-12-31 23:00:00","2070-12-31 00:00:00","2
071-12-30 23:50:00","2071-12-31 19:30:00","2073-01-01 12:00:00","2069-12-31 12:00:00","2072-01-01 01:00:00","2073-01-01 06:00:00","2071-12-31 17:00:00","2070
-12-31 23:00:00","2068-12-31 12:00:00","2071-01-01 01:00:00","2071-12-31 22:00:00","2072-12-31 17:00:00","2072-01-01 06:00:00","2072-12-31 22:00:00","2072-01
-01 07:00:00","2072-01-01 09:00:00","2071-12-31 09:50:00","2022-09-13 09:00:00","2022-10-25 09:00:00","2072-01-01 04:00:00","2022-09-22 09:00:00","2022-11-03
09:30:00","2022-11-03 10:00:00","2022-11-08 10:00:00","2072-12-31 19:30:00","2073-01-01 04:00:00","2022-11-03 11:00:00","2022-11-04 10:00:00","2072-12-31 09
:50:00","2021-07-20 13:00:00","2022-08-29 10:00:00","2022-09-15 09:00:00","2022-10-26 10:00:00","2070-12-31 22:00:00","2072-12-31 01:40:00","2022-09-02 10:00
:00","2022-09-20 10:00:00","2022-09-22 13:00:00","2022-10-11 09:00:00","2022-10-12 10:00:00","2022-10-14 09:00:00","2022-10-18 14:00:00","2022-10-20 10:00:00
","2022-10-27 10:00:00","2022-11-02 11:00:00","2022-11-02 15:00:00","2073-01-01 07:00:00","2022-09-07 10:00:00","2022-09-08 09:30:00","2022-09-21 13:00:00","
2022-09-26 13:00:00","2022-09-30 09:00:00","2022-10-06 09:00:00","2022-10-10 12:30:00","2022-10-13 09:00:00","2022-10-13 14:00:00","2022-10-14 09:30:00","202
--More--
2-10-19 10:00:00","2022-10-24 12:00:00","2022-10-26 08:00:00","2022-10-26 09:00:00","2022-10-28 09:00:00","2022-11-07 09:00:00","2071-01-01 11:00:00","2071-1
2-31 00:00:00","2072-12-31 00:50:00","2021-06-24 13:00:00","2022-08-11 10:00:00","2022-08-25 09:00:00","2022-09-07 09:00:00","2022-09-08 09:00:00","2022-09-0
9 10:00:00","2022-09-21 08:00:00","2022-09-23 09:00:00","2022-09-26 12:00:00","2022-09-27 10:00:00","2022-09-29 09:00:00","2022-10-04 09:00:00","2022-10-04 1
0:00:00","2022-10-05 10:00:00","2022-10-11 08:00:00","2022-10-18 13:00:00","2022-10-21 08:30:00","2022-10-21 10:00:00","2022-10-26 13:00:00","2022-10-28 10:0
0:00","2022-11-02 14:00:00"}
most_common_freqs | {0.2536,0.20986667,0.092766665,0.0090666665,0.0086,0.0065333336,0.0046,0.0029333334,0.0026666666,0.0025,0.0024333333,0.0023333333,0.
0020666667,0.0018666667,0.0018,0.0017666667,0.0015333333,0.0014333334,0.0013333333,0.0012,0.0011333333,0.0010666667,0.0010333334,0.001,0.0009,0.00073333335,0
.0007,0.00066666666,0.0006,0.00056666665,0.00053333334,0.00053333334,0.0005,0.00046666668,0.00046666668,0.00046666668,0.00046666668,0.00046666668,0.000466666
68,0.00043333333,0.00043333333,0.00043333333,0.0004,0.0004,0.0004,0.0004,0.0004,0.0004,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,
0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00036666667,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.0
0033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.00033333333,0.0003
3333333,0.00033333333,0.00033333333,0.00033333333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.
0003,0.0003,0.0003,0.0003,0.0003,0.0003}
histogram_bounds | {"2008-05-22 05:59:00","2021-06-08 06:30:00","2021-06-09 12:20:00","2021-06-11 11:00:00","2021-06-15 11:30:00","2021-06-17 08:00:00"
,"2021-06-21 09:00:00","2021-06-23 08:00:00","2021-06-24 14:00:00","2021-06-28 14:30:00","2021-06-30 12:20:00","2021-07-02 12:00:00","2021-07-07 07:30:00","2
021-07-08 12:45:00","2021-07-12 15:30:00","2021-07-14 15:30:00","2021-07-19 11:00:00","2021-07-21 10:00:00","2021-07-26 09:30:00","2021-07-28 13:00:00","2021
-07-30 12:30:00","2021-08-04 08:00:00","2021-08-06 08:00:00","2021-08-10 15:00:00","2021-08-13 08:30:00","2021-08-17 12:30:00","2021-08-19 14:30:00","2021-08
-24 07:20:00","2021-08-26 07:50:00","2021-08-30 09:30:00","2021-09-01 08:45:00","2021-09-03 07:30:00","2021-09-07 11:30:00","2021-09-09 12:30:00","2021-09-14
08:00:00","2021-09-22 09:30:00","2021-11-25 15:00:00","2022-06-07 12:30:00","2022-08-08 08:00:00","2022-08-09 14:30:00","2022-08-11 07:00:00","2022-08-12 11
:30:00","2022-08-16 10:00:00","2022-08-17 21:30:00","2022-08-19 09:00:00","2022-08-22 14:00:00","2022-08-24 07:30:00","2022-08-25 10:00:00","2022-08-28 11:36
:10","2022-08-30 09:20:00","2022-08-31 12:30:00","2022-09-01 13:30:00","2022-09-05 08:09:00","2022-09-06 10:35:00","2022-09-07 14:00:00","2022-09-08 20:36:10
","2022-09-12 09:00:00","2022-09-13 11:00:00","2022-09-14 12:00:00","2022-09-15 12:30:00","2022-09-16 13:30:00","2022-09-19 14:00:00","2022-09-21 00:00:00","
2022-09-22 10:00:00","2022-09-23 13:30:00","2022-09-27 07:30:00","2022-09-27 15:15:00","2022-09-29 08:45:00","2022-09-30 10:45:00","2022-10-04 08:30:00","202
2-10-05 14:00:00","2022-10-07 06:00:00","2022-10-10 09:30:00","2022-10-11 13:00:00","2022-10-12 14:00:00","2022-10-13 19:15:00","2022-10-17 08:00:00","2022-1
0-18 09:00:00","2022-10-19 11:00:00","2022-10-20 12:30:00","2022-10-21 13:45:00","2022-10-24 14:00:00","2022-10-25 14:05:00","2022-10-27 07:10:00","2022-10-2
8 04:30:00","2022-10-31 10:45:00","2022-11-02 09:30:00","2022-11-03 10:55:00","2022-11-04 09:00:00","2022-11-07 09:30:00","2022-11-08 08:00:00","2022-11-09 1
2:00:00","2022-11-10 17:00:00","2022-11-15 10:30:00","2022-11-22 09:45:00","2022-12-05 23:00:00","2023-01-31 14:30:00","2070-05-01 13:58:39","2071-02-20 17:4
4:52","2072-02-18 13:59:59","2073-01-01 13:00:00"}
correlation | 0.28662655
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
