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 | 20200619223716.GO1497@aart.rice.edu Whole thread Raw |
| In response to | Re: PostgreSQL 12.3 slow index scan chosen (Kenneth Marshall <ktm@rice.edu>) |
| List | pgsql-performance |
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
pgsql-performance by date: