Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mG5UQ-0006FT-8a for pgsql-admin@arkaria.postgresql.org; Tue, 17 Aug 2021 20:15:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mG5UN-0006iM-Eh for pgsql-admin@arkaria.postgresql.org; Tue, 17 Aug 2021 20:15:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mG5UM-0006iD-NN for pgsql-admin@lists.postgresql.org; Tue, 17 Aug 2021 20:15:11 +0000 Received: from mail-oi1-x231.google.com ([2607:f8b0:4864:20::231]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mG5UI-00084N-Id for pgsql-admin@lists.postgresql.org; Tue, 17 Aug 2021 20:15:09 +0000 Received: by mail-oi1-x231.google.com with SMTP id w6so880332oiv.11 for ; Tue, 17 Aug 2021 13:15:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=dVWeeUs4W9u9xBLM8skOAJsEoci0OGoTbMiBLWGM++k=; b=Tb4TPSA+UuCSZlr6Vy5RF2HTbb2CVqc9/DZY3G0rTJ41Mc1Cpi+cUAo7jtALbFBlUh QoeVr92IHJBU8ojlzmLO8s/4pyz29PqPRNUs++ZVB9SYAU2rHhC6VR20mZYizCj0wuBv 1MrgCRH8ATomtKsqnOIC92YDCRCyuELUgfOpVxGTMrt3xpeXQRmgDHAEsk53a92UYwRG VCisZRNd41VavG47Pj8AE0M/75DpV/8gQQYoKXztfcktFx7uJEGM7C7loG1oeu4Jf2Ma upWkGxwv9Kob45mBfsrFU1s24vBFUPNcQVxw8f0LXoE8FRcGZjLqCJyA+OXyAXh5I/As fghQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=dVWeeUs4W9u9xBLM8skOAJsEoci0OGoTbMiBLWGM++k=; b=RqsATMcn8hWnhq6gcn6azENEnKno3OL+8OCeJJ5G7lnnB39qOTwpE+/km+hELg+bFv dQWM/UjkcImvsUlpU47WAcJioQEkbC0sqO+ETAZNj/uxepxHVHyzqRc1x6hLMH1+i3ab w1Z1FawutdiKYANZkEWUhz/HSv5l7KGyDSsEGnN6NzQ8vQRDRp3GUwpuetxXR/sRjlHz w3yWoEsO9FHkkOicyeXLOfDlA1t1XNLdGqHSKp0K4WlMMEdfH7pA9Xs+SdHwokBJRwOV NZw4lsrdAFNd/VFT2vQOYqIsgw2RW8Fm6dhUIFrt/lDE3Y8zPjHxe/psXNfvv8Suht6f xgsg== X-Gm-Message-State: AOAM531zgTMLpNZjn1U7tTVaS6FQcFg5zKxod9QkcV5N59gN7Y7v/RQ6 aLFhrHiB3fjJ5Vx406iky6iZ83CsFCv7Og== X-Google-Smtp-Source: ABdhPJyQdhm5O8xX1MIYJ/Bewqs6LsUROmAVdVk6WRj6TNOTsDRwz3WUk+oeAXkX4NBkzJlvSPpkGA== X-Received: by 2002:a54:4114:: with SMTP id l20mr3616523oic.178.1629231305508; Tue, 17 Aug 2021 13:15:05 -0700 (PDT) Received: from [192.168.88.10] (ip174-67-141-204.no.no.cox.net. [174.67.141.204]) by smtp.googlemail.com with ESMTPSA id r7sm458432oog.48.2021.08.17.13.15.04 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 17 Aug 2021 13:15:04 -0700 (PDT) Subject: Re: Join for the parent table will not leverage the index scan To: pgsql-admin@lists.postgresql.org References: <51ac99c9-300d-d60f-e2c3-2c3965babae1@gmail.com> From: Ron Message-ID: Date: Tue, 17 Aug 2021 15:15:02 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------C4B9B0DED6CFB2C3DF66C897" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------C4B9B0DED6CFB2C3DF66C897 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit I've seen a similar problem in v12, but this is different since it's a LIST partition, and I use RANGE partitions. The bottom line is that the query optimizer thinks it cheaper to sequentially scan.  Is there an index on client_id which I missed? On 8/16/21 10:25 PM, Shrikant Bhende wrote: > Hi Ron, > > Thanks for the reply and clarification. > Tables are getting analyzed regularly also did analze today as well, > Below is the test case where I tried to use the partition key in join, > but still getting the same result. > > > explain select * from dm_ci360.page_details pd left join > dm_ci360.page_details_ext pde on (pd.detail_id = pde.detail_id and > *pd.client_id = pde.client_id*); -- "QUERY PLAN" Gather > (cost=1328436468.62..1375694672.28 rows=421291719 width=8287) Workers > Planned: 8 -> Merge Left Join (cost=1328435468.62..1333564500.38 > rows=52661465 width=8287) Merge Cond: (((pd.detail_id)::text = > (pde.detail_id)::text) AND (pd.client_id = pde.client_id)) -> Sort > (cost=1045342980.27..1045489637.23 rows=58662783 width=8061) " Sort Key: > pd.detail_id, pd.client_id" -> Append (cost=0.00..24208020.84 > rows=58662783 width=8061) -> Parallel Seq Scan on page_details_null pd > (cost=0.00..2425406.85 rows=5120384 width=8220) -> Parallel Seq Scan on > page_details_m3 pd_1 (cost=0.00..503325.67 rows=1484767 width=8216) -> > Parallel Seq Scan on page_details_m2 pd_2 (cost=0.00..41493.07 rows=158208 > width=8253) -> Parallel Seq Scan on page_details_m1 pd_3 > (cost=0.00..298542.32 rows=718532 width=8281) -> Parallel Seq Scan on > page_details_5 pd_4 (cost=0.00..4546231.40 rows=9936540 width=8202) -> > Parallel Seq Scan on page_details_31 pd_5 (cost=0.00..5351785.68 > rows=13473668 width=7721) -> Parallel Seq Scan on page_details_49 pd_6 > (cost=0.00..1528222.35 rows=3763935 width=7721) -> Parallel Seq Scan on > page_details_52 pd_7 (cost=0.00..1005739.96 rows=2113496 width=8297) -> > Parallel Seq Scan on page_details_59 pd_8 (cost=0.00..2139754.44 > rows=4577244 width=8276) -> Parallel Seq Scan on page_details_60 pd_9 > (cost=0.00..316209.20 rows=846820 width=8263) -> Parallel Seq Scan on > page_details_61 pd_10 (cost=0.00..14790.59 rows=85759 width=8182) -> > Parallel Seq Scan on page_details_63 pd_11 (cost=0.00..52206.08 > rows=204008 width=8250) -> Parallel Seq Scan on page_details_64 pd_12 > (cost=0.00..358858.43 rows=904143 width=8314) -> Parallel Seq Scan on > page_details_66 pd_13 (cost=0.00..8647.65 rows=50365 width=8277) -> > Parallel Seq Scan on page_details_67 pd_14 (cost=0.00..8507.65 rows=54465 > width=8227) -> Parallel Seq Scan on page_details_68 pd_15 > (cost=0.00..7667.92 rows=47192 width=8259) -> Parallel Seq Scan on > page_details_78 pd_16 (cost=0.00..227673.87 rows=698287 width=8263) -> > Parallel Seq Scan on page_details_98 pd_17 (cost=0.00..342905.89 > rows=975589 width=8225) -> Parallel Seq Scan on page_details_101 pd_18 > (cost=0.00..277631.48 rows=815948 width=8205) -> Parallel Seq Scan on > page_details_130 pd_19 (cost=0.00..489453.45 rows=1409344 width=8199) -> > Parallel Seq Scan on page_details_m100 pd_20 (cost=0.00..8943.50 > rows=62750 width=8225) -> Parallel Seq Scan on page_details_m4 pd_21 > (cost=0.00..119599.55 rows=409355 width=8195) -> Parallel Seq Scan on > page_details_134 pd_22 (cost=0.00..1078678.24 rows=2791724 width=8176) -> > Parallel Seq Scan on page_details_58 pd_23 (cost=0.00..19107.17 > rows=114617 width=8362) -> Parallel Seq Scan on page_details_69 pd_24 > (cost=0.00..11774.27 rows=56027 width=8268) -> Parallel Seq Scan on > page_details_100 pd_25 (cost=0.00..14765.61 rows=83161 width=8195) -> > Parallel Seq Scan on page_details_114 pd_26 (cost=0.00..17953.55 > rows=112155 width=8183) -> Parallel Seq Scan on page_details_m5 pd_27 > (cost=0.00..2069930.14 rows=4803014 width=8225) -> Parallel Seq Scan on > page_details_32 pd_28 (cost=0.00..9655.67 rows=90767 width=8807) -> > Parallel Seq Scan on page_details_23 pd_29 (cost=0.00..1183.66 rows=14966 > width=9327) -> Parallel Seq Scan on page_details_m6 pd_30 > (cost=0.00..331066.68 rows=766068 width=7814) -> Parallel Seq Scan on > page_details_m7 pd_31 (cost=0.00..276835.86 rows=820386 width=8203) -> > Parallel Seq Scan on page_details_m8 pd_32 (cost=0.00..219677.99 > rows=764299 width=8200) -> Parallel Seq Scan on page_details_144 pd_33 > (cost=0.00..1243.71 rows=10671 width=8189) -> Parallel Seq Scan on > page_details_143 pd_34 (cost=0.00..79279.91 rows=304892 width=8196) -> > Parallel Seq Scan on page_details_145 pd_35 (cost=0.00..3271.37 rows=19237 > width=8226) -> Materialize (cost=283092488.35..285385333.09 rows=458568949 > width=226) -> Sort (cost=283092488.35..284238910.72 rows=458568949 > width=226) " Sort Key: pde.detail_id, pde.client_id" -> Append > (cost=0.00..16497547.49 rows=458568949 width=226) -> Seq Scan on > page_details_ext_null pde (cost=0.00..1524481.42 rows=42223842 width=226) > -> Seq Scan on page_details_ext_m3 pde_1 (cost=0.00..347731.78 > rows=9980078 width=226) -> Seq Scan on page_details_ext_m2 pde_2 > (cost=0.00..26437.83 rows=721783 width=226) -> Seq Scan on > page_details_ext_m1 pde_3 (cost=0.00..180092.12 rows=4793812 width=226) -> > Seq Scan on page_details_ext_5 pde_4 (cost=0.00..3157905.57 rows=84945757 > width=226) -> Seq Scan on page_details_ext_31 pde_5 (cost=0.00..4198600.83 > rows=125384283 width=226) -> Seq Scan on page_details_ext_49 pde_6 > (cost=0.00..1058695.45 rows=29083145 width=226) -> Seq Scan on > page_details_ext_52 pde_7 (cost=0.00..582580.50 rows=16175450 width=226) > -> Seq Scan on page_details_ext_59 pde_8 (cost=0.00..1276447.65 > rows=34184965 width=226) -> Seq Scan on page_details_ext_60 pde_9 > (cost=0.00..209865.67 rows=5987267 width=226) -> Seq Scan on > page_details_ext_61 pde_10 (cost=0.00..10846.58 rows=301558 width=226) -> > Seq Scan on page_details_ext_63 pde_11 (cost=0.00..32991.51 rows=897951 > width=226) -> Seq Scan on page_details_ext_64 pde_12 (cost=0.00..211218.56 > rows=6212356 width=226) -> Seq Scan on page_details_ext_66 pde_13 > (cost=0.00..4888.06 rows=127506 width=226) -> Seq Scan on > page_details_ext_67 pde_14 (cost=0.00..5320.29 rows=139829 width=226) -> > Seq Scan on page_details_ext_68 pde_15 (cost=0.00..4519.19 rows=121219 > width=226) -> Seq Scan on page_details_ext_78 pde_16 (cost=0.00..138728.11 > rows=3774711 width=226) -> Seq Scan on page_details_ext_98 pde_17 > (cost=0.00..235653.77 rows=6628477 width=226) -> Seq Scan on > page_details_ext_101 pde_18 (cost=0.00..195898.44 rows=5365744 width=226) > -> Seq Scan on page_details_ext_130 pde_19 (cost=0.00..327932.65 > rows=8781965 width=226) -> Seq Scan on page_details_ext_m100 pde_20 > (cost=0.00..4913.38 rows=143438 width=226) -> Seq Scan on > page_details_ext_m4 pde_21 (cost=0.00..79001.77 rows=2065777 width=226) -> > Seq Scan on page_details_ext_134 pde_22 (cost=0.00..757767.21 > rows=19952321 width=226) -> Seq Scan on page_details_ext_58 pde_23 > (cost=0.00..12171.64 rows=361364 width=331) -> Seq Scan on > page_details_ext_69 pde_24 (cost=0.00..6617.58 rows=176558 width=226) -> > Seq Scan on page_details_ext_100 pde_25 (cost=0.00..9531.93 rows=261993 > width=226) -> Seq Scan on page_details_ext_114 pde_26 (cost=0.00..11702.72 > rows=358372 width=226) -> Seq Scan on page_details_ext_m5 pde_27 > (cost=0.00..1300373.49 rows=33923749 width=226) -> Seq Scan on > page_details_ext_23 pde_28 (cost=0.00..819.39 rows=25239 width=378) -> Seq > Scan on page_details_ext_32 pde_29 (cost=0.00..7009.04 rows=217504 > width=378) -> Seq Scan on page_details_ext_m6 pde_30 (cost=0.00..181575.92 > rows=4720692 width=226) -> Seq Scan on page_details_ext_m7 pde_31 > (cost=0.00..196632.54 rows=5332554 width=226) -> Seq Scan on > page_details_ext_m8 pde_32 (cost=0.00..142816.57 rows=3877857 width=226) > -> Seq Scan on page_details_ext_144 pde_33 (cost=0.00..780.22 rows=18422 > width=226) -> Seq Scan on page_details_ext_143 pde_34 (cost=0.00..53050.54 > rows=1255354 width=226) -> Seq Scan on page_details_ext_145 pde_35 > (cost=0.00..1947.57 rows=46057 width=226) > > > Thanks > > On Mon, Aug 16, 2021 at 9:47 AM Ron > wrote: > > When were the tables last analyzed? > > Also, the whole set of partitions must be scanned, since the tables > are partitioned by a field other than what's in the JOIN and WHERE > clauses. > > On 8/13/21 7:23 AM, Shrikant Bhende wrote: >> >> We have two tables, both have ~36 partitions. >> This is my first query, which attempts to join the parent tables on >> the columns that have been used to create multi-column indexes for >> all partitions (multi-column indexes defined on session_id, detail_id >> in that order): >> >> *PostgreSQL version : 10 >> *-- *query* >> >> explain >> >> select * from dm_ci360.page_details pd >> inner join dm_ci360.page_details_ext pde >>  on (pd.session_id = pde.session_id >>  and pd.detail_id = pde.detail_id) >> where pd.session_dt >= now()::date-2; >> >> -- *plan* >> >> Hash Join  (cost=36927955.75..100106605.07 rows=11 width=8355) >> >>   Hash Cond: (((pd.session_id)::text = (pde.session_id)::text) AND >> ((pd.detail_id)::text = (pde.detail_id)::text)) >> >>   ->  Append  (cost=1297.33..571870.20 rows=630535 width=8130) >> >>         ->  Bitmap Heap Scan on page_details_null pd >>  (cost=1297.33..240207.85 rows=69259 width=8220) >> >>               Recheck Cond: (session_dt >= ((now())::date - 2)) >> >>               ->  Bitmap Index Scan on >> page_details_null_session_dt_idx  (cost=0.00..1280.02 rows=69259 width=0) >> >>                     Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m3_session_dt_idx on >> page_details_m3 pd_1  (cost=0.44..4.71 rows=1 width=8216) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m2_session_dt_idx on >> page_details_m2 pd_2  (cost=0.43..102.79 rows=621 width=8253) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m1_session_dt_idx on >> page_details_m1 pd_3  (cost=0.44..4.55 rows=1 width=8281) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_5_session_dt_idx on >> page_details_5 pd_4  (cost=0.57..51622.53 rows=196574 width=8202) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Bitmap Heap Scan on page_details_31 pd_5 >>  (cost=982.61..194857.98 rows=52391 width=7721) >> >>               Recheck Cond: (session_dt >= ((now())::date - 2)) >> >>               ->  Bitmap Index Scan on >> page_details_31_session_dt_idx1  (cost=0.00..969.51 rows=52391 width=0) >> >>                     Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_49_session_dt_idx on >> page_details_49 pd_6  (cost=0.45..10991.08 rows=50039 width=7721) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_52_session_dt_idx on >> page_details_52 pd_7  (cost=0.44..2337.94 rows=10222 width=8297) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_59_session_dt_idx on >> page_details_59 pd_8  (cost=0.57..24790.17 rows=50148 width=8276) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_60_session_dt_idx on >> page_details_60 pd_9  (cost=0.44..7292.41 rows=4259 width=8263) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_61_session_dt_idx on >> page_details_61 pd_10  (cost=0.43..309.10 rows=704 width=8182) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_63_session_dt_idx on >> page_details_63 pd_11  (cost=0.43..13.20 rows=25 width=8250) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_64_session_dt_idx on >> page_details_64 pd_12  (cost=0.44..5.41 rows=1 width=8314) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_66_session_dt_idx on >> page_details_66 pd_13  (cost=0.42..4.49 rows=1 width=8277) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_67_session_dt_idx on >> page_details_67 pd_14  (cost=0.42..58.62 rows=325 width=8227) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_68_session_dt_idx on >> page_details_68 pd_15  (cost=0.42..35.20 rows=111 width=8259) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_78_session_dt_idx on >> page_details_78 pd_16  (cost=0.44..1738.10 rows=4608 width=8263) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_98_session_dt_idx on >> page_details_98 pd_17  (cost=0.44..2157.31 rows=7136 width=8225) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_101_session_dt_idx on >> page_details_101 pd_18  (cost=0.44..2956.03 rows=9241 width=8205) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_130_session_dt_idx on >> page_details_130 pd_19  (cost=0.44..1683.53 rows=19656 width=8199) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m100_session_dt_idx on >> page_details_m100 pd_20  (cost=0.43..4.96 rows=1 width=8225) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m4_session_dt_idx on >> page_details_m4 pd_21  (cost=0.43..659.89 rows=6097 width=8195) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_134_session_dt_idx on >> page_details_134 pd_22  (cost=0.45..4666.25 rows=50409 width=8176) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_58_session_dt_idx on >> page_details_58 pd_23  (cost=0.43..7.05 rows=1 width=8362) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_69_session_dt_idx on >> page_details_69 pd_24  (cost=0.43..125.03 rows=348 width=8268) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_100_session_dt_idx on >> page_details_100 pd_25  (cost=0.43..381.13 rows=466 width=8195) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_114_session_dt_idx on >> page_details_114 pd_26  (cost=0.43..7.80 rows=1 width=8183) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m5_session_dt_idx on >> page_details_m5 pd_27  (cost=0.57..4505.18 rows=50872 width=8225) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_32_session_dt_idx on >> page_details_32 pd_28  (cost=0.43..8.44 rows=1 width=8807) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_23_session_dt_idx on >> page_details_23 pd_29  (cost=0.29..7.47 rows=1 width=9327) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m6_session_dt_idx on >> page_details_m6 pd_30  (cost=0.44..769.32 rows=7135 width=7814) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m7_session_dt_idx on >> page_details_m7 pd_31  (cost=0.44..11400.50 rows=14694 width=8203) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_m8_session_dt_idx on >> page_details_m8 pd_32  (cost=0.44..6568.86 rows=8045 width=8200) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_144_session_dt_idx on >> page_details_144 pd_33  (cost=0.29..21.16 rows=162 width=8189) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_143_session_dt_idx on >> page_details_143 pd_34  (cost=0.43..1477.00 rows=16135 width=8197) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>         ->  Index Scan using page_details_145_session_dt_idx on >> page_details_145 pd_35  (cost=0.29..87.17 rows=844 width=8226) >> >>               Index Cond: (session_dt >= ((now())::date - 2)) >> >>   ->  Hash  (cost=16117516.77..16117516.77 rows=449927377 width=226) >> >>         ->  Append  (cost=0.00..16117516.77 rows=449927377 width=226) >> >>               ->  Seq Scan on page_details_ext_null pde >>  (cost=0.00..1467668.52 rows=40845752 width=226) >> >>               ->  Seq Scan on page_details_ext_m3 pde_1 >>  (cost=0.00..347732.32 rows=9980132 width=226) >> >>               ->  Seq Scan on page_details_ext_m2 pde_2 >>  (cost=0.00..26146.27 rows=715527 width=226) >> >>               ->  Seq Scan on page_details_ext_m1 pde_3 >>  (cost=0.00..180093.08 rows=4793908 width=226) >> >>               ->  Seq Scan on page_details_ext_5 pde_4 >>  (cost=0.00..3056150.08 rows=82652608 width=226) >> >>               ->  Seq Scan on page_details_ext_31 pde_5 >>  (cost=0.00..4177416.08 rows=124953208 width=226) >> >>               ->  Seq Scan on page_details_ext_49 pde_6 >>  (cost=0.00..1034364.84 rows=28538484 width=226) >> >>               ->  Seq Scan on page_details_ext_52 pde_7 >>  (cost=0.00..575529.72 rows=16018272 width=226) >> >>               ->  Seq Scan on page_details_ext_59 pde_8 >>  (cost=0.00..1226617.78 rows=33054378 width=226) >> >>               ->  Seq Scan on page_details_ext_60 pde_9 >>  (cost=0.00..206925.60 rows=5920260 width=226) >> >>               ->  Seq Scan on page_details_ext_61 pde_10 >>  (cost=0.00..10599.63 rows=296463 width=226) >> >>               ->  Seq Scan on page_details_ext_63 pde_11 >>  (cost=0.00..32993.45 rows=898145 width=226) >> >>               ->  Seq Scan on page_details_ext_64 pde_12 >>  (cost=0.00..211220.00 rows=6212500 width=226) >> >>               ->  Seq Scan on page_details_ext_66 pde_13 >>  (cost=0.00..4888.44 rows=127544 width=226) >> >>               ->  Seq Scan on page_details_ext_67 pde_14 >>  (cost=0.00..5121.02 rows=135402 width=226) >> >>               ->  Seq Scan on page_details_ext_68 pde_15 >>  (cost=0.00..4284.18 rows=116218 width=226) >> >>               ->  Seq Scan on page_details_ext_78 pde_16 >>  (cost=0.00..135663.64 rows=3705564 width=226) >> >>               ->  Seq Scan on page_details_ext_98 pde_17 >>  (cost=0.00..232421.44 rows=6556944 width=226) >> >>               ->  Seq Scan on page_details_ext_101 pde_18 >>  (cost=0.00..189844.81 rows=5231481 width=226) >> >>               ->  Seq Scan on page_details_ext_130 pde_19 >>  (cost=0.00..318011.40 rows=8558640 width=226) >> >>               ->  Seq Scan on page_details_ext_m100 pde_20 >>  (cost=0.00..4914.04 rows=143504 width=226) >> >>               ->  Seq Scan on page_details_ext_m4 pde_21 >>  (cost=0.00..75878.68 rows=1994468 width=226) >> >>               ->  Seq Scan on page_details_ext_134 pde_22 >>  (cost=0.00..724264.36 rows=19192136 width=226) >> >>               ->  Seq Scan on page_details_ext_58 pde_23 >>  (cost=0.00..12171.94 rows=361394 width=331) >> >>               ->  Seq Scan on page_details_ext_69 pde_24 >>  (cost=0.00..6464.95 rows=173295 width=226) >> >>               ->  Seq Scan on page_details_ext_100 pde_25 >>  (cost=0.00..9217.39 rows=255239 width=226) >> >>               ->  Seq Scan on page_details_ext_114 pde_26 >>  (cost=0.00..11702.69 rows=358469 width=226) >> >>               ->  Seq Scan on page_details_ext_m5 pde_27 >>  (cost=0.00..1274434.84 rows=33330284 width=226) >> >>               ->  Seq Scan on page_details_ext_23 pde_28 >>  (cost=0.00..822.03 rows=25503 width=378) >> >>               ->  Seq Scan on page_details_ext_32 pde_29 >>  (cost=0.00..7011.56 rows=217756 width=378) >> >>               ->  Seq Scan on page_details_ext_m6 pde_30 >>  (cost=0.00..177039.58 rows=4616658 width=226) >> >>               ->  Seq Scan on page_details_ext_m7 pde_31 >>  (cost=0.00..188264.43 rows=5143643 width=226) >> >>               ->  Seq Scan on page_details_ext_m8 pde_32 >>  (cost=0.00..139185.56 rows=3799056 width=226) >> >>               ->  Seq Scan on page_details_ext_144 pde_33 >>  (cost=0.00..687.46 rows=16246 width=226) >> >>               ->  Seq Scan on page_details_ext_143 pde_34 >>  (cost=0.00..41053.82 rows=971482 width=226) >> >>               ->  Seq Scan on page_details_ext_145 pde_35 >>  (cost=0.00..711.14 rows=16814 width=226) >> >> >> >> The only place an index scan is used is to filter down page_details. >> In my second query, >> when explicitly joining single partitions an index scan is used to >> perform filtering, and the join as well: >> >> --*query* >> >> explain >> >> select * from dm_ci360.page_details_5 pd >> inner join dm_ci360.page_details_ext_5 pde >> on (pd.session_id = pde.session_id >> and pd.detail_id = pde.detail_id) >> where pd.session_dt >= now()::date-2; >> >> >> --*plan* >> >> Nested Loop  (cost=1.27..1685980.38 rows=1 width=8428) >> >>   ->  Index Scan using page_details_5_session_dt_idx on >> page_details_5 pd  (cost=0.57..51267.67 rows=196574 width=8202) >> >>         Index Cond: (session_dt >= ((now())::date - 2)) >> >>   ->  Index Scan using page_details_ext_5_session_id_detail_id_idx on >> page_details_ext_5 pde  (cost=0.69..8.31 rows=1 width=226) >> >>         Index Cond: (((session_id)::text = (pd.session_id)::text) AND >> ((detail_id)::text = (pd.detail_id)::text)) >> >> I just cannot figure out why the join for the parent table will not >> leverage the index, >> but the join for a single partition will. We are running on Postgres >> 10, and the row counts for the tables can be found below: >> >> *Row Count* >> page_details -> ~413M >> page_details_ext -> ~450M >> >> *Table DDL* >> * >> * >> |-- page_details CREATE TABLE dm_ci360.page_details ( dml_id int4 >> NULL, dml_ts timestamp NULL, dml_ts_utc timestamp NULL, dml_type >> varchar(1) NULL, dwh_job_id int4 NULL, ins_dwh_job_id int4 NULL, >> upd_dwh_job_id int4 NULL, deleted_flag varchar(1) NULL, >> processed_dttm timestamp NULL, valid_from_dttm timestamp(0) NULL, >> valid_to_dttm timestamp(0) NULL, ccd varchar(2000) NULL, client_id >> int4 NULL, detail_id varchar(32) NULL, session_id varchar(29) NULL, >> visit_id varchar(32) NULL, window_size_txt varchar(20) NULL, >> session_dt date NULL, page_url_txt varchar(1332) NULL, domain_nm >> varchar(165) NULL, bytes_sent_cnt int4 NULL, page_load_sec_cnt int8 >> NULL, page_complete_sec_cnt int8 NULL, protocol_nm varchar(8) NULL, >> page_desc varchar(1332) NULL, class1_id varchar(650) NULL, class2_id >> varchar(650) NULL, class3_id varchar(650) NULL, class4_id >> varchar(650) NULL, class5_id varchar(650) NULL, class6_id >> varchar(650) NULL, class7_id varchar(650) NULL, class8_id >> varchar(650) NULL, class9_id varchar(650) NULL, class10_id >> varchar(650) NULL, class11_id varchar(650) NULL, class12_id >> varchar(650) NULL, class13_id varchar(650) NULL, class14_id >> varchar(650) NULL, class15_id varchar(650) NULL, url_domain >> varchar(215) NULL, identity_id varchar(36) NULL, detail_dttm >> timestamp NULL, detail_dttm_tz timestamp NULL, load_dttm timestamp >> NULL, session_dt_tz date NULL, detail_id_hex varchar(32) NULL, >> visit_id_hex varchar(32) NULL, session_id_hex varchar(29) NULL ) >> PARTITION BY LIST (client_id); CREATE TABLE dm_ci360.page_details_100 >> PARTITION OF dm_ci360.page_details FOR VALUES IN (100); CREATE INDEX >> page_details_100_detail_dttm_idx ON dm_ci360.page_details_100 USING >> btree (detail_dttm) CREATE INDEX page_details_100_identity_id_idx ON >> dm_ci360.page_details_100 USING btree (identity_id) CREATE INDEX >> page_details_100_session_dt_idx ON dm_ci360.page_details_100 USING >> btree (session_dt) CREATE INDEX >> page_details_100_session_id_detail_id_idx ON >> dm_ci360.page_details_100 USING btree (session_id, detail_id) -- >> page_details_ext CREATE TABLE dm_ci360.page_details_ext ( dml_id int4 >> NULL, dml_ts timestamp NULL, dml_ts_utc timestamp NULL, dml_type >> varchar(1) NULL, dwh_job_id int4 NULL, ins_dwh_job_id int4 NULL, >> upd_dwh_job_id int4 NULL, deleted_flag varchar(1) NULL, >> processed_dttm timestamp NULL, valid_from_dttm timestamp(0) NULL, >> valid_to_dttm timestamp(0) NULL, ccd varchar(2000) NULL, client_id >> int4 NULL, detail_id varchar(32) NULL, session_id varchar(29) NULL, >> active_sec_spent_on_page_cnt int4 NULL, seconds_spent_on_page_cnt >> int4 NULL, load_dttm timestamp NULL, detail_id_hex varchar(32) NULL, >> session_id_hex varchar(29) NULL ) PARTITION BY LIST (client_id); >> CREATE TABLE dm_ci360.page_details_ext_100 PARTITION OF >> dm_ci360.page_details_ext FOR VALUES IN (100); CREATE INDEX >> page_details_ext_100_session_id_detail_id_idx ON >> dm_ci360.page_details_ext_100 USING btree (session_id, detail_id)| >> CREATE INDEX page_details_ext_100_session_id_detail_id_idx ON >> dm_ci360.page_details_ext_100 USING btree (session_id, detail_id) >> Thanks > > -- > Angular momentum makes the world go 'round. > -- Angular momentum makes the world go 'round. --------------C4B9B0DED6CFB2C3DF66C897 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit I've seen a similar problem in v12, but this is different since it's a LIST partition, and I use RANGE partitions.

The bottom line is that the query optimizer thinks it cheaper to sequentially scan.  Is there an index on client_id which I missed?

On 8/16/21 10:25 PM, Shrikant Bhende wrote:
Hi Ron, 

Thanks for the reply and clarification. 
Tables are getting analyzed regularly also did analze today as well, 
Below is the test case where I tried to use the partition key in join, 
but still getting the same result.


explain
select *
from dm_ci360.page_details pd
left join dm_ci360.page_details_ext pde
    on (pd.detail_id = pde.detail_id
        and pd.client_id = pde.client_id);
    
    --
    
    "QUERY PLAN"
Gather  (cost=1328436468.62..1375694672.28 rows=421291719 width=8287)
  Workers Planned: 8
  ->  Merge Left Join  (cost=1328435468.62..1333564500.38 rows=52661465 width=8287)
        Merge Cond: (((pd.detail_id)::text = (pde.detail_id)::text) AND (pd.client_id = pde.client_id))
        ->  Sort  (cost=1045342980.27..1045489637.23 rows=58662783 width=8061)
"              Sort Key: pd.detail_id, pd.client_id"
              ->  Append  (cost=0.00..24208020.84 rows=58662783 width=8061)
                    ->  Parallel Seq Scan on page_details_null pd  (cost=0.00..2425406.85 rows=5120384 width=8220)
                    ->  Parallel Seq Scan on page_details_m3 pd_1  (cost=0.00..503325.67 rows=1484767 width=8216)
                    ->  Parallel Seq Scan on page_details_m2 pd_2  (cost=0.00..41493.07 rows=158208 width=8253)
                    ->  Parallel Seq Scan on page_details_m1 pd_3  (cost=0.00..298542.32 rows=718532 width=8281)
                    ->  Parallel Seq Scan on page_details_5 pd_4  (cost=0.00..4546231.40 rows=9936540 width=8202)
                    ->  Parallel Seq Scan on page_details_31 pd_5  (cost=0.00..5351785.68 rows=13473668 width=7721)
                    ->  Parallel Seq Scan on page_details_49 pd_6  (cost=0.00..1528222.35 rows=3763935 width=7721)
                    ->  Parallel Seq Scan on page_details_52 pd_7  (cost=0.00..1005739.96 rows=2113496 width=8297)
                    ->  Parallel Seq Scan on page_details_59 pd_8  (cost=0.00..2139754.44 rows=4577244 width=8276)
                    ->  Parallel Seq Scan on page_details_60 pd_9  (cost=0.00..316209.20 rows=846820 width=8263)
                    ->  Parallel Seq Scan on page_details_61 pd_10  (cost=0.00..14790.59 rows=85759 width=8182)
                    ->  Parallel Seq Scan on page_details_63 pd_11  (cost=0.00..52206.08 rows=204008 width=8250)
                    ->  Parallel Seq Scan on page_details_64 pd_12  (cost=0.00..358858.43 rows=904143 width=8314)
                    ->  Parallel Seq Scan on page_details_66 pd_13  (cost=0.00..8647.65 rows=50365 width=8277)
                    ->  Parallel Seq Scan on page_details_67 pd_14  (cost=0.00..8507.65 rows=54465 width=8227)
                    ->  Parallel Seq Scan on page_details_68 pd_15  (cost=0.00..7667.92 rows=47192 width=8259)
                    ->  Parallel Seq Scan on page_details_78 pd_16  (cost=0.00..227673.87 rows=698287 width=8263)
                    ->  Parallel Seq Scan on page_details_98 pd_17  (cost=0.00..342905.89 rows=975589 width=8225)
                    ->  Parallel Seq Scan on page_details_101 pd_18  (cost=0.00..277631.48 rows=815948 width=8205)
                    ->  Parallel Seq Scan on page_details_130 pd_19  (cost=0.00..489453.45 rows=1409344 width=8199)
                    ->  Parallel Seq Scan on page_details_m100 pd_20  (cost=0.00..8943.50 rows=62750 width=8225)
                    ->  Parallel Seq Scan on page_details_m4 pd_21  (cost=0.00..119599.55 rows=409355 width=8195)
                    ->  Parallel Seq Scan on page_details_134 pd_22  (cost=0.00..1078678.24 rows=2791724 width=8176)
                    ->  Parallel Seq Scan on page_details_58 pd_23  (cost=0.00..19107.17 rows=114617 width=8362)
                    ->  Parallel Seq Scan on page_details_69 pd_24  (cost=0.00..11774.27 rows=56027 width=8268)
                    ->  Parallel Seq Scan on page_details_100 pd_25  (cost=0.00..14765.61 rows=83161 width=8195)
                    ->  Parallel Seq Scan on page_details_114 pd_26  (cost=0.00..17953.55 rows=112155 width=8183)
                    ->  Parallel Seq Scan on page_details_m5 pd_27  (cost=0.00..2069930.14 rows=4803014 width=8225)
                    ->  Parallel Seq Scan on page_details_32 pd_28  (cost=0.00..9655.67 rows=90767 width=8807)
                    ->  Parallel Seq Scan on page_details_23 pd_29  (cost=0.00..1183.66 rows=14966 width=9327)
                    ->  Parallel Seq Scan on page_details_m6 pd_30  (cost=0.00..331066.68 rows=766068 width=7814)
                    ->  Parallel Seq Scan on page_details_m7 pd_31  (cost=0.00..276835.86 rows=820386 width=8203)
                    ->  Parallel Seq Scan on page_details_m8 pd_32  (cost=0.00..219677.99 rows=764299 width=8200)
                    ->  Parallel Seq Scan on page_details_144 pd_33  (cost=0.00..1243.71 rows=10671 width=8189)
                    ->  Parallel Seq Scan on page_details_143 pd_34  (cost=0.00..79279.91 rows=304892 width=8196)
                    ->  Parallel Seq Scan on page_details_145 pd_35  (cost=0.00..3271.37 rows=19237 width=8226)
        ->  Materialize  (cost=283092488.35..285385333.09 rows=458568949 width=226)
              ->  Sort  (cost=283092488.35..284238910.72 rows=458568949 width=226)
"                    Sort Key: pde.detail_id, pde.client_id"
                    ->  Append  (cost=0.00..16497547.49 rows=458568949 width=226)
                          ->  Seq Scan on page_details_ext_null pde  (cost=0.00..1524481.42 rows=42223842 width=226)
                          ->  Seq Scan on page_details_ext_m3 pde_1  (cost=0.00..347731.78 rows=9980078 width=226)
                          ->  Seq Scan on page_details_ext_m2 pde_2  (cost=0.00..26437.83 rows=721783 width=226)
                          ->  Seq Scan on page_details_ext_m1 pde_3  (cost=0.00..180092.12 rows=4793812 width=226)
                          ->  Seq Scan on page_details_ext_5 pde_4  (cost=0.00..3157905.57 rows=84945757 width=226)
                          ->  Seq Scan on page_details_ext_31 pde_5  (cost=0.00..4198600.83 rows=125384283 width=226)
                          ->  Seq Scan on page_details_ext_49 pde_6  (cost=0.00..1058695.45 rows=29083145 width=226)
                          ->  Seq Scan on page_details_ext_52 pde_7  (cost=0.00..582580.50 rows=16175450 width=226)
                          ->  Seq Scan on page_details_ext_59 pde_8  (cost=0.00..1276447.65 rows=34184965 width=226)
                          ->  Seq Scan on page_details_ext_60 pde_9  (cost=0.00..209865.67 rows=5987267 width=226)
                          ->  Seq Scan on page_details_ext_61 pde_10  (cost=0.00..10846.58 rows=301558 width=226)
                          ->  Seq Scan on page_details_ext_63 pde_11  (cost=0.00..32991.51 rows=897951 width=226)
                          ->  Seq Scan on page_details_ext_64 pde_12  (cost=0.00..211218.56 rows=6212356 width=226)
                          ->  Seq Scan on page_details_ext_66 pde_13  (cost=0.00..4888.06 rows=127506 width=226)
                          ->  Seq Scan on page_details_ext_67 pde_14  (cost=0.00..5320.29 rows=139829 width=226)
                          ->  Seq Scan on page_details_ext_68 pde_15  (cost=0.00..4519.19 rows=121219 width=226)
                          ->  Seq Scan on page_details_ext_78 pde_16  (cost=0.00..138728.11 rows=3774711 width=226)
                          ->  Seq Scan on page_details_ext_98 pde_17  (cost=0.00..235653.77 rows=6628477 width=226)
                          ->  Seq Scan on page_details_ext_101 pde_18  (cost=0.00..195898.44 rows=5365744 width=226)
                          ->  Seq Scan on page_details_ext_130 pde_19  (cost=0.00..327932.65 rows=8781965 width=226)
                          ->  Seq Scan on page_details_ext_m100 pde_20  (cost=0.00..4913.38 rows=143438 width=226)
                          ->  Seq Scan on page_details_ext_m4 pde_21  (cost=0.00..79001.77 rows=2065777 width=226)
                          ->  Seq Scan on page_details_ext_134 pde_22  (cost=0.00..757767.21 rows=19952321 width=226)
                          ->  Seq Scan on page_details_ext_58 pde_23  (cost=0.00..12171.64 rows=361364 width=331)
                          ->  Seq Scan on page_details_ext_69 pde_24  (cost=0.00..6617.58 rows=176558 width=226)
                          ->  Seq Scan on page_details_ext_100 pde_25  (cost=0.00..9531.93 rows=261993 width=226)
                          ->  Seq Scan on page_details_ext_114 pde_26  (cost=0.00..11702.72 rows=358372 width=226)
                          ->  Seq Scan on page_details_ext_m5 pde_27  (cost=0.00..1300373.49 rows=33923749 width=226)
                          ->  Seq Scan on page_details_ext_23 pde_28  (cost=0.00..819.39 rows=25239 width=378)
                          ->  Seq Scan on page_details_ext_32 pde_29  (cost=0.00..7009.04 rows=217504 width=378)
                          ->  Seq Scan on page_details_ext_m6 pde_30  (cost=0.00..181575.92 rows=4720692 width=226)
                          ->  Seq Scan on page_details_ext_m7 pde_31  (cost=0.00..196632.54 rows=5332554 width=226)
                          ->  Seq Scan on page_details_ext_m8 pde_32  (cost=0.00..142816.57 rows=3877857 width=226)
                          ->  Seq Scan on page_details_ext_144 pde_33  (cost=0.00..780.22 rows=18422 width=226)
                          ->  Seq Scan on page_details_ext_143 pde_34  (cost=0.00..53050.54 rows=1255354 width=226)
                          ->  Seq Scan on page_details_ext_145 pde_35  (cost=0.00..1947.57 rows=46057 width=226)


Thanks 

On Mon, Aug 16, 2021 at 9:47 AM Ron <ronljohnsonjr@gmail.com> wrote:
When were the tables last analyzed?

Also, the whole set of partitions must be scanned, since the tables are partitioned by a field other than what's in the JOIN and WHERE clauses.

On 8/13/21 7:23 AM, Shrikant Bhende wrote:

We have two tables, both have ~36 partitions.
This is my first query, which attempts to join the parent tables on the columns that have been used to create multi-column indexes for all partitions (multi-column indexes defined on session_id, detail_id in that order):

PostgreSQL version : 10
-- query

explain

select * from dm_ci360.page_details pd
inner join dm_ci360.page_details_ext pde
 on (pd.session_id = pde.session_id
 and pd.detail_id = pde.detail_id)
where pd.session_dt >= now()::date-2;

-- plan

Hash Join  (cost=36927955.75..100106605.07 rows=11 width=8355)

  Hash Cond: (((pd.session_id)::text = (pde.session_id)::text) AND ((pd.detail_id)::text = (pde.detail_id)::text))

  ->  Append  (cost=1297.33..571870.20 rows=630535 width=8130)

        ->  Bitmap Heap Scan on page_details_null pd  (cost=1297.33..240207.85 rows=69259 width=8220)

              Recheck Cond: (session_dt >= ((now())::date - 2))

              ->  Bitmap Index Scan on page_details_null_session_dt_idx  (cost=0.00..1280.02 rows=69259 width=0)

                    Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m3_session_dt_idx on page_details_m3 pd_1  (cost=0.44..4.71 rows=1 width=8216)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m2_session_dt_idx on page_details_m2 pd_2  (cost=0.43..102.79 rows=621 width=8253)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m1_session_dt_idx on page_details_m1 pd_3  (cost=0.44..4.55 rows=1 width=8281)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_5_session_dt_idx on page_details_5 pd_4  (cost=0.57..51622.53 rows=196574 width=8202)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Bitmap Heap Scan on page_details_31 pd_5  (cost=982.61..194857.98 rows=52391 width=7721)

              Recheck Cond: (session_dt >= ((now())::date - 2))

              ->  Bitmap Index Scan on page_details_31_session_dt_idx1  (cost=0.00..969.51 rows=52391 width=0)

                    Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_49_session_dt_idx on page_details_49 pd_6  (cost=0.45..10991.08 rows=50039 width=7721)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_52_session_dt_idx on page_details_52 pd_7  (cost=0.44..2337.94 rows=10222 width=8297)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_59_session_dt_idx on page_details_59 pd_8  (cost=0.57..24790.17 rows=50148 width=8276)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_60_session_dt_idx on page_details_60 pd_9  (cost=0.44..7292.41 rows=4259 width=8263)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_61_session_dt_idx on page_details_61 pd_10  (cost=0.43..309.10 rows=704 width=8182)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_63_session_dt_idx on page_details_63 pd_11  (cost=0.43..13.20 rows=25 width=8250)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_64_session_dt_idx on page_details_64 pd_12  (cost=0.44..5.41 rows=1 width=8314)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_66_session_dt_idx on page_details_66 pd_13  (cost=0.42..4.49 rows=1 width=8277)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_67_session_dt_idx on page_details_67 pd_14  (cost=0.42..58.62 rows=325 width=8227)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_68_session_dt_idx on page_details_68 pd_15  (cost=0.42..35.20 rows=111 width=8259)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_78_session_dt_idx on page_details_78 pd_16  (cost=0.44..1738.10 rows=4608 width=8263)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_98_session_dt_idx on page_details_98 pd_17  (cost=0.44..2157.31 rows=7136 width=8225)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_101_session_dt_idx on page_details_101 pd_18  (cost=0.44..2956.03 rows=9241 width=8205)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_130_session_dt_idx on page_details_130 pd_19  (cost=0.44..1683.53 rows=19656 width=8199)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m100_session_dt_idx on page_details_m100 pd_20  (cost=0.43..4.96 rows=1 width=8225)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m4_session_dt_idx on page_details_m4 pd_21  (cost=0.43..659.89 rows=6097 width=8195)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_134_session_dt_idx on page_details_134 pd_22  (cost=0.45..4666.25 rows=50409 width=8176)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_58_session_dt_idx on page_details_58 pd_23  (cost=0.43..7.05 rows=1 width=8362)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_69_session_dt_idx on page_details_69 pd_24  (cost=0.43..125.03 rows=348 width=8268)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_100_session_dt_idx on page_details_100 pd_25  (cost=0.43..381.13 rows=466 width=8195)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_114_session_dt_idx on page_details_114 pd_26  (cost=0.43..7.80 rows=1 width=8183)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m5_session_dt_idx on page_details_m5 pd_27  (cost=0.57..4505.18 rows=50872 width=8225)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_32_session_dt_idx on page_details_32 pd_28  (cost=0.43..8.44 rows=1 width=8807)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_23_session_dt_idx on page_details_23 pd_29  (cost=0.29..7.47 rows=1 width=9327)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m6_session_dt_idx on page_details_m6 pd_30  (cost=0.44..769.32 rows=7135 width=7814)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m7_session_dt_idx on page_details_m7 pd_31  (cost=0.44..11400.50 rows=14694 width=8203)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_m8_session_dt_idx on page_details_m8 pd_32  (cost=0.44..6568.86 rows=8045 width=8200)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_144_session_dt_idx on page_details_144 pd_33  (cost=0.29..21.16 rows=162 width=8189)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_143_session_dt_idx on page_details_143 pd_34  (cost=0.43..1477.00 rows=16135 width=8197)

              Index Cond: (session_dt >= ((now())::date - 2))

        ->  Index Scan using page_details_145_session_dt_idx on page_details_145 pd_35  (cost=0.29..87.17 rows=844 width=8226)

              Index Cond: (session_dt >= ((now())::date - 2))

  ->  Hash  (cost=16117516.77..16117516.77 rows=449927377 width=226)

        ->  Append  (cost=0.00..16117516.77 rows=449927377 width=226)

              ->  Seq Scan on page_details_ext_null pde  (cost=0.00..1467668.52 rows=40845752 width=226)

              ->  Seq Scan on page_details_ext_m3 pde_1  (cost=0.00..347732.32 rows=9980132 width=226)

              ->  Seq Scan on page_details_ext_m2 pde_2  (cost=0.00..26146.27 rows=715527 width=226)

              ->  Seq Scan on page_details_ext_m1 pde_3  (cost=0.00..180093.08 rows=4793908 width=226)

              ->  Seq Scan on page_details_ext_5 pde_4  (cost=0.00..3056150.08 rows=82652608 width=226)

              ->  Seq Scan on page_details_ext_31 pde_5  (cost=0.00..4177416.08 rows=124953208 width=226)

              ->  Seq Scan on page_details_ext_49 pde_6  (cost=0.00..1034364.84 rows=28538484 width=226)

              ->  Seq Scan on page_details_ext_52 pde_7  (cost=0.00..575529.72 rows=16018272 width=226)

              ->  Seq Scan on page_details_ext_59 pde_8  (cost=0.00..1226617.78 rows=33054378 width=226)

              ->  Seq Scan on page_details_ext_60 pde_9  (cost=0.00..206925.60 rows=5920260 width=226)

              ->  Seq Scan on page_details_ext_61 pde_10  (cost=0.00..10599.63 rows=296463 width=226)

              ->  Seq Scan on page_details_ext_63 pde_11  (cost=0.00..32993.45 rows=898145 width=226)

              ->  Seq Scan on page_details_ext_64 pde_12  (cost=0.00..211220.00 rows=6212500 width=226)

              ->  Seq Scan on page_details_ext_66 pde_13  (cost=0.00..4888.44 rows=127544 width=226)

              ->  Seq Scan on page_details_ext_67 pde_14  (cost=0.00..5121.02 rows=135402 width=226)

              ->  Seq Scan on page_details_ext_68 pde_15  (cost=0.00..4284.18 rows=116218 width=226)

              ->  Seq Scan on page_details_ext_78 pde_16  (cost=0.00..135663.64 rows=3705564 width=226)

              ->  Seq Scan on page_details_ext_98 pde_17  (cost=0.00..232421.44 rows=6556944 width=226)

              ->  Seq Scan on page_details_ext_101 pde_18  (cost=0.00..189844.81 rows=5231481 width=226)

              ->  Seq Scan on page_details_ext_130 pde_19  (cost=0.00..318011.40 rows=8558640 width=226)

              ->  Seq Scan on page_details_ext_m100 pde_20  (cost=0.00..4914.04 rows=143504 width=226)

              ->  Seq Scan on page_details_ext_m4 pde_21  (cost=0.00..75878.68 rows=1994468 width=226)

              ->  Seq Scan on page_details_ext_134 pde_22  (cost=0.00..724264.36 rows=19192136 width=226)

              ->  Seq Scan on page_details_ext_58 pde_23  (cost=0.00..12171.94 rows=361394 width=331)

              ->  Seq Scan on page_details_ext_69 pde_24  (cost=0.00..6464.95 rows=173295 width=226)

              ->  Seq Scan on page_details_ext_100 pde_25  (cost=0.00..9217.39 rows=255239 width=226)

              ->  Seq Scan on page_details_ext_114 pde_26  (cost=0.00..11702.69 rows=358469 width=226)

              ->  Seq Scan on page_details_ext_m5 pde_27  (cost=0.00..1274434.84 rows=33330284 width=226)

              ->  Seq Scan on page_details_ext_23 pde_28  (cost=0.00..822.03 rows=25503 width=378)

              ->  Seq Scan on page_details_ext_32 pde_29  (cost=0.00..7011.56 rows=217756 width=378)

              ->  Seq Scan on page_details_ext_m6 pde_30  (cost=0.00..177039.58 rows=4616658 width=226)

              ->  Seq Scan on page_details_ext_m7 pde_31  (cost=0.00..188264.43 rows=5143643 width=226)

              ->  Seq Scan on page_details_ext_m8 pde_32  (cost=0.00..139185.56 rows=3799056 width=226)

              ->  Seq Scan on page_details_ext_144 pde_33  (cost=0.00..687.46 rows=16246 width=226)

              ->  Seq Scan on page_details_ext_143 pde_34  (cost=0.00..41053.82 rows=971482 width=226)

              ->  Seq Scan on page_details_ext_145 pde_35  (cost=0.00..711.14 rows=16814 width=226)

 

The only place an index scan is used is to filter down page_details. In my second query,
when explicitly joining single partitions an index scan is used to perform filtering, and the join as well:

--query

explain

select * from dm_ci360.page_details_5 pd
inner join dm_ci360.page_details_ext_5 pde
on (pd.session_id = pde.session_id
and pd.detail_id = pde.detail_id)
where pd.session_dt >= now()::date-2;


--plan

Nested Loop  (cost=1.27..1685980.38 rows=1 width=8428)

  ->  Index Scan using page_details_5_session_dt_idx on page_details_5 pd  (cost=0.57..51267.67 rows=196574 width=8202)

        Index Cond: (session_dt >= ((now())::date - 2))

  ->  Index Scan using page_details_ext_5_session_id_detail_id_idx on page_details_ext_5 pde  (cost=0.69..8.31 rows=1 width=226)

        Index Cond: (((session_id)::text = (pd.session_id)::text) AND ((detail_id)::text = (pd.detail_id)::text))

I just cannot figure out why the join for the parent table will not leverage the index,
but the join for a single partition will. We are running on Postgres 10, and the row counts for the tables can be found below:

Row Count
page_details -> ~413M
page_details_ext -> ~450M

Table DDL

-- page_details

CREATE TABLE dm_ci360.page_details (
    dml_id int4 NULL,
    dml_ts timestamp NULL,
    dml_ts_utc timestamp NULL,
    dml_type varchar(1) NULL,
    dwh_job_id int4 NULL,
    ins_dwh_job_id int4 NULL,
    upd_dwh_job_id int4 NULL,
    deleted_flag varchar(1) NULL,
    processed_dttm timestamp NULL,
    valid_from_dttm timestamp(0) NULL,
    valid_to_dttm timestamp(0) NULL,
    ccd varchar(2000) NULL,
    client_id int4 NULL,
    detail_id varchar(32) NULL,
    session_id varchar(29) NULL,
    visit_id varchar(32) NULL,
    window_size_txt varchar(20) NULL,
    session_dt date NULL,
    page_url_txt varchar(1332) NULL,
    domain_nm varchar(165) NULL,
    bytes_sent_cnt int4 NULL,
    page_load_sec_cnt int8 NULL,
    page_complete_sec_cnt int8 NULL,
    protocol_nm varchar(8) NULL,
    page_desc varchar(1332) NULL,
    class1_id varchar(650) NULL,
    class2_id varchar(650) NULL,
    class3_id varchar(650) NULL,
    class4_id varchar(650) NULL,
    class5_id varchar(650) NULL,
    class6_id varchar(650) NULL,
    class7_id varchar(650) NULL,
    class8_id varchar(650) NULL,
    class9_id varchar(650) NULL,
    class10_id varchar(650) NULL,
    class11_id varchar(650) NULL,
    class12_id varchar(650) NULL,
    class13_id varchar(650) NULL,
    class14_id varchar(650) NULL,
    class15_id varchar(650) NULL,
    url_domain varchar(215) NULL,
    identity_id varchar(36) NULL,
    detail_dttm timestamp NULL,
    detail_dttm_tz timestamp NULL,
    load_dttm timestamp NULL,
    session_dt_tz date NULL,
    detail_id_hex varchar(32) NULL,
    visit_id_hex varchar(32) NULL,
    session_id_hex varchar(29) NULL
)
PARTITION BY LIST (client_id);

CREATE TABLE dm_ci360.page_details_100 PARTITION OF dm_ci360.page_details FOR VALUES IN (100);

CREATE INDEX page_details_100_detail_dttm_idx ON dm_ci360.page_details_100 USING btree (detail_dttm)
CREATE INDEX page_details_100_identity_id_idx ON dm_ci360.page_details_100 USING btree (identity_id)
CREATE INDEX page_details_100_session_dt_idx ON dm_ci360.page_details_100 USING btree (session_dt)
CREATE INDEX page_details_100_session_id_detail_id_idx ON dm_ci360.page_details_100 USING btree (session_id, detail_id)

-- page_details_ext

CREATE TABLE dm_ci360.page_details_ext (
    dml_id int4 NULL,
    dml_ts timestamp NULL,
    dml_ts_utc timestamp NULL,
    dml_type varchar(1) NULL,
    dwh_job_id int4 NULL,
    ins_dwh_job_id int4 NULL,
    upd_dwh_job_id int4 NULL,
    deleted_flag varchar(1) NULL,
    processed_dttm timestamp NULL,
    valid_from_dttm timestamp(0) NULL,
    valid_to_dttm timestamp(0) NULL,
    ccd varchar(2000) NULL,
    client_id int4 NULL,
    detail_id varchar(32) NULL,
    session_id varchar(29) NULL,
    active_sec_spent_on_page_cnt int4 NULL,
    seconds_spent_on_page_cnt int4 NULL,
    load_dttm timestamp NULL,
    detail_id_hex varchar(32) NULL,
    session_id_hex varchar(29) NULL
)
PARTITION BY LIST (client_id);

CREATE TABLE dm_ci360.page_details_ext_100 PARTITION OF dm_ci360.page_details_ext FOR VALUES IN (100);

CREATE INDEX page_details_ext_100_session_id_detail_id_idx ON dm_ci360.page_details_ext_100 USING btree (session_id, detail_id)
CREATE INDEX page_details_ext_100_session_id_detail_id_idx ON dm_ci360.page_details_ext_100 USING btree (session_id, detail_id)
Thanks

--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.
--------------C4B9B0DED6CFB2C3DF66C897--