Re: PostgreSQL 12.3 slow index scan chosen

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

 



On Fri, Jun 19, 2020 at 05:25:33PM -0500, Kenneth Marshall wrote:
> 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
> 

Here is another query that is showing the same selection of an index
scan when without it is is soooo much faster:

# explain (analyze,buffers)  SELECT COUNT(DISTINCT main.id) FROM Assets
# main JOIN Groups Groups_1  ON ( LOWER(Groups_1.Domain) =
# 'rt::asset-role' ) AND ( Groups_1.Instance = main.id ) JOIN
# CachedGroupMembers CachedGroupMembers_2  ON (
# CachedGroupMembers_2.Disabled = '0' ) AND (
# CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE ( (
# CachedGroupMembers_2.MemberId = '151395' ) ) AND (LOWER(main.Status)
# != 'deleted');
                                                                       QUERY
PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=12488.19..12488.20 rows=1 width=8) (actual
time=46.438..46.439 rows=1 loops=1)
   Buffers: shared hit=40111
   ->  Nested Loop  (cost=364.48..12488.19 rows=1 width=4) (actual
time=46.402..46.402 rows=0 loops=1)
         Buffers: shared hit=40111
         ->  Hash Join  (cost=363.16..12343.59 rows=59 width=8) (actual
time=4.111..11.633 rows=13194 loops=1)
               Hash Cond: (groups_1.instance = main.id)
               Buffers: shared hit=529
               ->  Bitmap Heap Scan on groups groups_1
(cost=186.22..12132.46 rows=13028 width=8) (actual time=0.918..3.492
rows=13380 loops=1)
                     Recheck Cond: (lower((domain)::text) =
'rt::asset-role'::text)
                     Heap Blocks: exact=390
                     Buffers: shared hit=474
                     ->  Bitmap Index Scan on groups2
(cost=0.00..182.97 rows=13028 width=0) (actual time=0.879..0.879
rows=13380 loops=1)
                           Index Cond: (lower((domain)::text) =
'rt::asset-role'::text)
                           Buffers: shared hit=84
               ->  Hash  (cost=121.66..121.66 rows=4422 width=4) (actual
time=3.174..3.174 rows=4398 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 219kB
                     Buffers: shared hit=55
                     ->  Seq Scan on assets main  (cost=0.00..121.66
rows=4422 width=4) (actual time=0.014..2.425 rows=4398 loops=1)
                           Filter: (lower((status)::text) <>
'deleted'::text)
                           Rows Removed by Filter: 47
                           Buffers: shared hit=55
         ->  Bitmap Heap Scan on cachedgroupmembers cachedgroupmembers_2
(cost=1.32..2.44 rows=1 width=4) (actual time=0.002..0.002 rows=0
loops=13194)
               Recheck Cond: ((groupid = groups_1.id) AND (memberid =
151395) AND (disabled = '0'::smallint))
               Buffers: shared hit=39582
               ->  Bitmap Index Scan on disgroumem  (cost=0.00..1.32
rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=13194)
                     Index Cond: ((groupid = groups_1.id) AND (memberid
= 151395) AND (disabled = '0'::smallint))
                     Buffers: shared hit=39582
 Planning Time: 0.520 ms
 Execution Time: 46.503 ms
(29 rows)

And with enable_indexscan = 1;

# explain (analyze,buffers)  SELECT COUNT(DISTINCT main.id) FROM Assets
# main JOIN Groups Groups_1  ON ( LOWER(Groups_1.Domain) =
# 'rt::asset-role' ) AND ( Groups_1.Instance = main.id ) JOIN
# CachedGroupMembers CachedGroupMembers_2  ON (
# CachedGroupMembers_2.Disabled = '0' ) AND (
# CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE ( (
# CachedGroupMembers_2.MemberId = '151395' ) ) AND (LOWER(main.Status)
# != 'deleted');
                                                                               QUERY
PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=563.50..563.51 rows=1 width=8) (actual
time=2626.584..2626.585 rows=1 loops=1)
   Buffers: shared hit=172390
   ->  Nested Loop  (cost=11.13..563.50 rows=1 width=4) (actual
time=2626.568..2626.568 rows=0 loops=1)
         Buffers: shared hit=172390
         ->  Merge Join  (cost=10.70..482.35 rows=59 width=8) (actual
time=0.352..2599.829 rows=13194 loops=1)
               Merge Cond: (main.id = groups_1.instance)
               Buffers: shared hit=132808
               ->  Index Scan using assets_pkey on assets main
(cost=0.28..160.81 rows=4422 width=4) (actual time=0.039..3.578
rows=4398 loops=1)
                     Filter: (lower((status)::text) <> 'deleted'::text)
                     Rows Removed by Filter: 47
                     Buffers: shared hit=103
               ->  Index Scan using groups3 on groups groups_1
(cost=0.43..130022.48 rows=13028 width=8) (actual time=0.296..2592.141
rows=13380 loops=1)
                     Filter: (lower((domain)::text) =
'rt::asset-role'::text)
                     Rows Removed by Filter: 3853979
                     Buffers: shared hit=132705
         ->  Index Only Scan using disgroumem on cachedgroupmembers
cachedgroupmembers_2  (cost=0.43..1.37 rows=1 width=4) (actual
time=0.002..0.002 rows=0 loops=13194)
               Index Cond: ((groupid = groups_1.id) AND (memberid =
151395) AND (disabled = '0'::smallint))
               Heap Fetches: 0
               Buffers: shared hit=39582
 Planning Time: 0.562 ms
 Execution Time: 2626.651 ms
(21 rows)

I'm not sure if it is just a pathological interaction of this
application with PostgreSQL or something I need to fix. Ideally, I could
figure out a way to have PostgreSQL do it automatically.

Regards,
Ken






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

  Powered by Linux