Re: PostgreSQL 12.3 slow index scan chosen - Mailing list pgsql-performance
| From | Kenneth Marshall |
|---|---|
| Subject | Re: PostgreSQL 12.3 slow index scan chosen |
| Date | |
| Msg-id | 20200619222533.GN1497@aart.rice.edu Whole thread Raw |
| In response to | Re: PostgreSQL 12.3 slow index scan chosen (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: PostgreSQL 12.3 slow index scan chosen
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: PostgreSQL 12.3 slow index scan chosen (Kenneth Marshall <ktm@rice.edu>) |
| List | pgsql-performance |
On Fri, Jun 19, 2020 at 06:10:34PM -0400, Tom Lane wrote:
> > max(objectcustomfieldvalues.objectid) = 28108423 and here is the
> > histogram for that column:
>
> ... 3304313,3693956,27667772}
>
> Hmm, does seem like you have some outlier keys. Are any of the keys in
> the column you're trying to join to larger than 27667772?
>
> regards, tom lane
Hi Tom,
The only values above 27667772? for objectid are:
# select * from objectcustomfieldvalues where objectid > 27667772;
id | objectid | customfield | content | creator |
created | lastupdatedby | lastupdated | objecttype |
largecontent | contenttype | contentencoding | sortorder | disabled
----------+----------+-------------+------------+---------+---------------------+---------------+---------------------+-----------------+--------------+-------------+-----------------+-----------+----------
19012927 | 27667773 | 375 | 2020-05-12 | 3768865 | 2020-05-13
16:10:39 | 3768865 | 2020-05-13 16:10:39 | RT::Transaction |
| | | 0 | 0
19012928 | 27667774 | 375 | 2020-05-12 | 3768865 | 2020-05-13
16:10:39 | 3768865 | 2020-05-13 16:10:39 | RT::Transaction |
| | | 0 | 0
19020166 | 27680053 | 375 | 2020-05-14 | 3570362 | 2020-05-14
14:14:20 | 3570362 | 2020-05-14 14:14:20 | RT::Transaction |
| | | 0 | 0
19025163 | 27688649 | 375 | 2020-05-13 | 3768865 | 2020-05-14
20:27:04 | 3768865 | 2020-05-14 20:27:04 | RT::Transaction |
| | | 0 | 0
19025164 | 27688650 | 375 | 2020-05-13 | 3768865 | 2020-05-14
20:27:04 | 3768865 | 2020-05-14 20:27:04 | RT::Transaction |
| | | 0 | 0
19025165 | 27688651 | 375 | 2020-05-13 | 3768865 | 2020-05-14
20:27:04 | 3768865 | 2020-05-14 20:27:04 | RT::Transaction |
| | | 0 | 0
19025166 | 27688652 | 375 | 2020-05-13 | 3768865 | 2020-05-14
20:27:04 | 3768865 | 2020-05-14 20:27:04 | RT::Transaction |
| | | 0 | 0
19025167 | 27688676 | 375 | 2020-05-14 | 3768865 | 2020-05-14
20:27:29 | 3768865 | 2020-05-14 20:27:29 | RT::Transaction |
| | | 0 | 0
19031374 | 27698358 | 375 | 2020-05-13 | 3768865 | 2020-05-15
15:32:57 | 3768865 | 2020-05-15 15:32:57 | RT::Transaction |
| | | 0 | 0
19031384 | 27698376 | 375 | 2020-05-14 | 3768865 | 2020-05-15
15:33:50 | 3768865 | 2020-05-15 15:33:50 | RT::Transaction |
| | | 0 | 0
19031385 | 27698377 | 375 | 2020-05-14 | 3768865 | 2020-05-15
15:33:50 | 3768865 | 2020-05-15 15:33:50 | RT::Transaction |
| | | 0 | 0
19033205 | 27701391 | 375 | 2020-05-15 | 3197295 | 2020-05-15
18:21:36 | 3197295 | 2020-05-15 18:21:36 | RT::Transaction |
| | | 0 | 0
19042369 | 27715839 | 375 | 2020-05-18 | 1403795 | 2020-05-18
14:12:35 | 1403795 | 2020-05-18 14:12:35 | RT::Transaction |
| | | 0 | 0
19047274 | 27723981 | 375 | 2020-05-18 | 3197295 | 2020-05-18
19:29:14 | 3197295 | 2020-05-18 19:29:14 | RT::Transaction |
| | | 0 | 0
19048566 | 27726800 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:18 | 3768865 | 2020-05-18 20:23:18 | RT::Transaction |
| | | 0 | 0
19048567 | 27726801 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:18 | 3768865 | 2020-05-18 20:23:18 | RT::Transaction |
| | | 0 | 0
19048568 | 27726802 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:18 | 3768865 | 2020-05-18 20:23:18 | RT::Transaction |
| | | 0 | 0
19048569 | 27726803 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:18 | 3768865 | 2020-05-18 20:23:18 | RT::Transaction |
| | | 0 | 0
19048570 | 27726804 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:18 | 3768865 | 2020-05-18 20:23:18 | RT::Transaction |
| | | 0 | 0
19048571 | 27726805 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:18 | 3768865 | 2020-05-18 20:23:18 | RT::Transaction |
| | | 0 | 0
19048572 | 27726806 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:19 | 3768865 | 2020-05-18 20:23:19 | RT::Transaction |
| | | 0 | 0
19048573 | 27726807 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:19 | 3768865 | 2020-05-18 20:23:19 | RT::Transaction |
| | | 0 | 0
19048574 | 27726808 | 375 | 2020-05-18 | 3768865 | 2020-05-18
20:23:19 | 3768865 | 2020-05-18 20:23:19 | RT::Transaction |
| | | 0 | 0
19054502 | 27738410 | 375 | 2020-05-19 | 3197295 | 2020-05-19
15:01:50 | 3197295 | 2020-05-19 15:01:50 | RT::Transaction |
| | | 0 | 0
19056348 | 27741653 | 375 | 2020-05-19 | 3768865 | 2020-05-19
16:39:01 | 3768865 | 2020-05-19 16:39:01 | RT::Transaction |
| | | 0 | 0
19056349 | 27741654 | 375 | 2020-05-19 | 3768865 | 2020-05-19
16:39:01 | 3768865 | 2020-05-19 16:39:01 | RT::Transaction |
| | | 0 | 0
19056350 | 27741655 | 375 | 2020-05-19 | 3768865 | 2020-05-19
16:39:02 | 3768865 | 2020-05-19 16:39:02 | RT::Transaction |
| | | 0 | 0
19056351 | 27741656 | 375 | 2020-05-19 | 3768865 | 2020-05-19
16:39:02 | 3768865 | 2020-05-19 16:39:02 | RT::Transaction |
| | | 0 | 0
19056352 | 27741657 | 375 | 2020-05-19 | 3768865 | 2020-05-19
16:39:02 | 3768865 | 2020-05-19 16:39:02 | RT::Transaction |
| | | 0 | 0
19056362 | 27741667 | 375 | 2020-05-19 | 3768865 | 2020-05-19
16:39:29 | 3768865 | 2020-05-19 16:39:29 | RT::Transaction |
| | | 0 | 0
19057464 | 27743793 | 375 | 2020-05-19 | 3197295 | 2020-05-19
18:03:16 | 3197295 | 2020-05-19 18:03:16 | RT::Transaction |
| | | 0 | 0
19067180 | 27760343 | 375 | 2020-05-20 | 1403795 | 2020-05-20
18:01:59 | 1403795 | 2020-05-20 18:01:59 | RT::Transaction |
| | | 0 | 0
19067476 | 27760892 | 375 | 2020-05-19 | 3197295 | 2020-05-20
18:23:48 | 3197295 | 2020-05-20 18:23:48 | RT::Transaction |
| | | 0 | 0
19073560 | 27771129 | 375 | 2020-05-21 | 3197295 | 2020-05-21
14:15:54 | 3197295 | 2020-05-21 14:15:54 | RT::Transaction |
| | | 0 | 0
19074011 | 27771902 | 375 | 2020-05-21 | 3570362 | 2020-05-21
15:02:49 | 3570362 | 2020-05-21 15:02:49 | RT::Transaction |
| | | 0 | 0
19081811 | 27784951 | 375 | 2020-05-22 | 2960471 | 2020-05-22
14:52:40 | 2960471 | 2020-05-22 14:52:40 | RT::Transaction |
| | | 0 | 0
19093560 | 27804234 | 375 | 2020-05-26 | 3570362 | 2020-05-26
15:00:29 | 3570362 | 2020-05-26 15:00:29 | RT::Transaction |
| | | 0 | 0
19094043 | 27805100 | 375 | 2020-05-26 | 3570362 | 2020-05-26
15:30:15 | 3570362 | 2020-05-26 15:30:15 | RT::Transaction |
| | | 0 | 0
19094798 | 27806250 | 375 | 2020-05-26 | 3570362 | 2020-05-26
15:59:22 | 3570362 | 2020-05-26 15:59:22 | RT::Transaction |
| | | 0 | 0
19103803 | 27822098 | 375 | 2020-05-27 | 3570362 | 2020-05-27
15:15:37 | 3570362 | 2020-05-27 15:15:37 | RT::Transaction |
| | | 0 | 0
19103893 | 27822211 | 375 | 2020-05-26 | 3768865 | 2020-05-27
15:20:11 | 3768865 | 2020-05-27 15:20:11 | RT::Transaction |
| | | 0 | 0
19103894 | 27822212 | 375 | 2020-05-26 | 3768865 | 2020-05-27
15:20:12 | 3768865 | 2020-05-27 15:20:12 | RT::Transaction |
| | | 0 | 0
19103895 | 27822213 | 375 | 2020-05-26 | 3768865 | 2020-05-27
15:20:12 | 3768865 | 2020-05-27 15:20:12 | RT::Transaction |
| | | 0 | 0
19103896 | 27822214 | 375 | 2020-05-26 | 3768865 | 2020-05-27
15:20:12 | 3768865 | 2020-05-27 15:20:12 | RT::Transaction |
| | | 0 | 0
19103897 | 27822215 | 375 | 2020-05-26 | 3768865 | 2020-05-27
15:20:12 | 3768865 | 2020-05-27 15:20:12 | RT::Transaction |
| | | 0 | 0
19103898 | 27822216 | 375 | 2020-05-26 | 3768865 | 2020-05-27
15:20:12 | 3768865 | 2020-05-27 15:20:12 | RT::Transaction |
| | | 0 | 0
19103899 | 27822217 | 375 | 2020-05-26 | 3768865 | 2020-05-27
15:20:12 | 3768865 | 2020-05-27 15:20:12 | RT::Transaction |
| | | 0 | 0
19103910 | 27822238 | 375 | 2020-05-27 | 3570362 | 2020-05-27
15:21:30 | 3570362 | 2020-05-27 15:21:30 | RT::Transaction |
| | | 0 | 0
19103921 | 27822243 | 375 | 2020-05-27 | 3768865 | 2020-05-27
15:21:39 | 3768865 | 2020-05-27 15:21:39 | RT::Transaction |
| | | 0 | 0
19103922 | 27822244 | 375 | 2020-05-27 | 3768865 | 2020-05-27
15:21:39 | 3768865 | 2020-05-27 15:21:39 | RT::Transaction |
| | | 0 | 0
19103923 | 27822245 | 375 | 2020-05-27 | 3768865 | 2020-05-27
15:21:39 | 3768865 | 2020-05-27 15:21:39 | RT::Transaction |
| | | 0 | 0
19103924 | 27822246 | 375 | 2020-05-27 | 3768865 | 2020-05-27
15:21:39 | 3768865 | 2020-05-27 15:21:39 | RT::Transaction |
| | | 0 | 0
19103925 | 27822247 | 375 | 2020-05-27 | 3768865 | 2020-05-27
15:21:39 | 3768865 | 2020-05-27 15:21:39 | RT::Transaction |
| | | 0 | 0
19109404 | 27830956 | 375 | 2020-05-27 | 3570362 | 2020-05-27
20:42:58 | 3570362 | 2020-05-27 20:42:58 | RT::Transaction |
| | | 0 | 0
19109462 | 27831009 | 375 | 2020-05-27 | 3570362 | 2020-05-27
20:44:12 | 3570362 | 2020-05-27 20:44:12 | RT::Transaction |
| | | 0 | 0
19115179 | 27840467 | 375 | 2020-05-28 | 3570362 | 2020-05-28
15:28:09 | 3570362 | 2020-05-28 15:28:09 | RT::Transaction |
| | | 0 | 0
19115214 | 27840551 | 375 | 2020-05-28 | 3570362 | 2020-05-28
15:29:59 | 3570362 | 2020-05-28 15:29:59 | RT::Transaction |
| | | 0 | 0
19118472 | 27845963 | 375 | 2020-05-28 | 3570362 | 2020-05-28
18:50:57 | 3570362 | 2020-05-28 18:50:57 | RT::Transaction |
| | | 0 | 0
19127210 | 27860753 | 375 | 2020-05-28 | 3768865 | 2020-05-29
17:22:57 | 3768865 | 2020-05-29 17:22:57 | RT::Transaction |
| | | 0 | 0
19127211 | 27860754 | 375 | 2020-05-28 | 3768865 | 2020-05-29
17:22:57 | 3768865 | 2020-05-29 17:22:57 | RT::Transaction |
| | | 0 | 0
19127212 | 27860755 | 375 | 2020-05-28 | 3768865 | 2020-05-29
17:22:57 | 3768865 | 2020-05-29 17:22:57 | RT::Transaction |
| | | 0 | 0
19127213 | 27860756 | 375 | 2020-05-28 | 3768865 | 2020-05-29
17:22:57 | 3768865 | 2020-05-29 17:22:57 | RT::Transaction |
| | | 0 | 0
19127214 | 27860757 | 375 | 2020-05-28 | 3768865 | 2020-05-29
17:22:57 | 3768865 | 2020-05-29 17:22:57 | RT::Transaction |
| | | 0 | 0
19163910 | 27922577 | 375 | 2020-06-02 | 3768865 | 2020-06-03
20:57:29 | 3768865 | 2020-06-03 20:57:29 | RT::Transaction |
| | | 0 | 0
19163911 | 27922578 | 375 | 2020-06-02 | 3768865 | 2020-06-03
20:57:29 | 3768865 | 2020-06-03 20:57:29 | RT::Transaction |
| | | 0 | 0
19163912 | 27922579 | 375 | 2020-06-02 | 3768865 | 2020-06-03
20:57:29 | 3768865 | 2020-06-03 20:57:29 | RT::Transaction |
| | | 0 | 0
19163913 | 27922580 | 375 | 2020-06-02 | 3768865 | 2020-06-03
20:57:29 | 3768865 | 2020-06-03 20:57:29 | RT::Transaction |
| | | 0 | 0
19163914 | 27922582 | 375 | 2020-06-01 | 3768865 | 2020-06-03
20:57:52 | 3768865 | 2020-06-03 20:57:52 | RT::Transaction |
| | | 0 | 0
19163915 | 27922583 | 375 | 2020-06-01 | 3768865 | 2020-06-03
20:57:52 | 3768865 | 2020-06-03 20:57:52 | RT::Transaction |
| | | 0 | 0
19163916 | 27922584 | 375 | 2020-06-01 | 3768865 | 2020-06-03
20:57:52 | 3768865 | 2020-06-03 20:57:52 | RT::Transaction |
| | | 0 | 0
19186439 | 27960807 | 375 | 2020-06-08 | 3197295 | 2020-06-08
16:18:49 | 3197295 | 2020-06-08 16:18:49 | RT::Transaction |
| | | 0 | 0
19189227 | 27965582 | 375 | 2020-06-08 | 22 | 2020-06-08
19:24:19 | 22 | 2020-06-08 19:24:19 | RT::Transaction |
| | | 0 | 0
19189269 | 27965637 | 375 | 2020-06-08 | 402 | 2020-06-08
19:25:20 | 402 | 2020-06-08 19:25:20 | RT::Transaction |
| | | 0 | 0
19189270 | 27965637 | 376 | 22 | 402 | 2020-06-08
19:25:20 | 402 | 2020-06-08 19:25:20 | RT::Transaction |
| | | 0 | 0
19189271 | 27965638 | 375 | 2020-06-08 | 402 | 2020-06-08
19:25:20 | 402 | 2020-06-08 19:25:20 | RT::Transaction |
| | | 0 | 0
19189272 | 27965638 | 376 | 22 | 402 | 2020-06-08
19:25:20 | 402 | 2020-06-08 19:25:20 | RT::Transaction |
| | | 0 | 0
19193472 | 27972893 | 375 | 2020-06-08 | 3197295 | 2020-06-09
12:21:50 | 3197295 | 2020-06-09 12:21:50 | RT::Transaction |
| | | 0 | 0
19204287 | 27991617 | 375 | 2020-06-10 | 3197295 | 2020-06-10
15:52:41 | 3197295 | 2020-06-10 15:52:41 | RT::Transaction |
| | | 0 | 0
19205446 | 27993528 | 375 | 2020-06-10 | 3768865 | 2020-06-10
17:24:43 | 3768865 | 2020-06-10 17:24:43 | RT::Transaction |
| | | 0 | 0
19226664 | 28019342 | 375 | 2020-06-10 | 3768865 | 2020-06-11
15:24:50 | 3768865 | 2020-06-11 15:24:50 | RT::Transaction |
| | | 0 | 0
19226665 | 28019343 | 375 | 2020-06-10 | 3768865 | 2020-06-11
15:24:50 | 3768865 | 2020-06-11 15:24:50 | RT::Transaction |
| | | 0 | 0
19226666 | 28019344 | 375 | 2020-06-10 | 3768865 | 2020-06-11
15:24:50 | 3768865 | 2020-06-11 15:24:50 | RT::Transaction |
| | | 0 | 0
19226667 | 28019345 | 375 | 2020-06-10 | 3768865 | 2020-06-11
15:24:50 | 3768865 | 2020-06-11 15:24:50 | RT::Transaction |
| | | 0 | 0
19233084 | 28030270 | 375 | 2020-06-12 | 3197295 | 2020-06-12
14:05:00 | 3197295 | 2020-06-12 14:05:00 | RT::Transaction |
| | | 0 | 0
19235815 | 28034687 | 375 | 2020-06-12 | 84 | 2020-06-12
17:57:02 | 84 | 2020-06-12 17:57:02 | RT::Transaction |
| | | 0 | 0
19236305 | 28035519 | 375 | 2020-06-12 | 3197295 | 2020-06-12
18:29:25 | 3197295 | 2020-06-12 18:29:25 | RT::Transaction |
| | | 0 | 0
19236386 | 28035692 | 375 | 2020-06-12 | 3197295 | 2020-06-12
18:36:56 | 3197295 | 2020-06-12 18:36:56 | RT::Transaction |
| | | 0 | 0
19237416 | 28037412 | 375 | 2020-06-12 | 3197295 | 2020-06-12
19:44:36 | 3197295 | 2020-06-12 19:44:36 | RT::Transaction |
| | | 0 | 0
19238015 | 28038402 | 375 | 2020-06-12 | 3768865 | 2020-06-12
20:26:15 | 3768865 | 2020-06-12 20:26:15 | RT::Transaction |
| | | 0 | 0
19238016 | 28038403 | 375 | 2020-06-12 | 3768865 | 2020-06-12
20:26:15 | 3768865 | 2020-06-12 20:26:15 | RT::Transaction |
| | | 0 | 0
19238017 | 28038404 | 375 | 2020-06-12 | 3768865 | 2020-06-12
20:26:15 | 3768865 | 2020-06-12 20:26:15 | RT::Transaction |
| | | 0 | 0
19238018 | 28038405 | 375 | 2020-06-12 | 3768865 | 2020-06-12
20:26:15 | 3768865 | 2020-06-12 20:26:15 | RT::Transaction |
| | | 0 | 0
19238032 | 28038422 | 375 | 2020-06-11 | 3768865 | 2020-06-12
20:26:39 | 3768865 | 2020-06-12 20:26:39 | RT::Transaction |
| | | 0 | 0
19238033 | 28038423 | 375 | 2020-06-11 | 3768865 | 2020-06-12
20:26:39 | 3768865 | 2020-06-12 20:26:39 | RT::Transaction |
| | | 0 | 0
19238034 | 28038424 | 375 | 2020-06-11 | 3768865 | 2020-06-12
20:26:39 | 3768865 | 2020-06-12 20:26:39 | RT::Transaction |
| | | 0 | 0
19238035 | 28038425 | 375 | 2020-06-11 | 3768865 | 2020-06-12
20:26:39 | 3768865 | 2020-06-12 20:26:39 | RT::Transaction |
| | | 0 | 0
19240041 | 28042208 | 375 | 2020-06-14 | 1403795 | 2020-06-14
12:50:47 | 1403795 | 2020-06-14 12:50:47 | RT::Transaction |
| | | 0 | 0
19242958 | 28046818 | 375 | 2020-06-15 | 3570362 | 2020-06-15
14:38:57 | 3570362 | 2020-06-15 14:38:57 | RT::Transaction |
| | | 0 | 0
19255465 | 28067560 | 375 | 2020-06-16 | 3570362 | 2020-06-16
18:41:13 | 3570362 | 2020-06-16 18:41:13 | RT::Transaction |
| | | 0 | 0
19279177 | 28108399 | 375 | 2020-06-18 | 3768865 | 2020-06-19
17:38:39 | 3768865 | 2020-06-19 17:38:39 | RT::Transaction |
| | | 0 | 0
19279178 | 28108400 | 375 | 2020-06-18 | 3768865 | 2020-06-19
17:38:39 | 3768865 | 2020-06-19 17:38:39 | RT::Transaction |
| | | 0 | 0
19279179 | 28108401 | 375 | 2020-06-18 | 3768865 | 2020-06-19
17:38:40 | 3768865 | 2020-06-19 17:38:40 | RT::Transaction |
| | | 0 | 0
19279180 | 28108402 | 375 | 2020-06-18 | 3768865 | 2020-06-19
17:38:40 | 3768865 | 2020-06-19 17:38:40 | RT::Transaction |
| | | 0 | 0
19279193 | 28108419 | 375 | 2020-06-17 | 3768865 | 2020-06-19
17:39:12 | 3768865 | 2020-06-19 17:39:12 | RT::Transaction |
| | | 0 | 0
19279194 | 28108420 | 375 | 2020-06-17 | 3768865 | 2020-06-19
17:39:12 | 3768865 | 2020-06-19 17:39:12 | RT::Transaction |
| | | 0 | 0
19279195 | 28108421 | 375 | 2020-06-17 | 3768865 | 2020-06-19
17:39:12 | 3768865 | 2020-06-19 17:39:12 | RT::Transaction |
| | | 0 | 0
19279196 | 28108422 | 375 | 2020-06-17 | 3768865 | 2020-06-19
17:39:12 | 3768865 | 2020-06-19 17:39:12 | RT::Transaction |
| | | 0 | 0
19279197 | 28108423 | 375 | 2020-06-17 | 3768865 | 2020-06-19
17:39:12 | 3768865 | 2020-06-19 17:39:12 | RT::Transaction |
| | | 0 | 0
They are just the time worked, so I do not understand why it is chosing
the crazy path that it does.
Regards,
Ken
pgsql-performance by date: