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
<https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/utils/adt/selfuncs.c>
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 |
