Declarative partitioning, UUIDs, index issues.

--0000000000005e1933059c08b88c
Content-Type: text/plain; charset="UTF-8"

Hi all. I have a table with 7 columns: uuid, date, smallint, smallint,
real, real, real.

The PK is the uuid, date, smallint, smallint.

It has 60 partitions on date, partitioned by month, back to 2015. Each
partition is roughly 40GB, about 350-450m rows.

Doing a join to this table using the uuid is not using an index, so it's
never completing. So where this table is b: SELECT * FROM a JOIN b ON
A.uuid = B.uuid - I thought it might use the PK as the uuid is the first
column, but we end up with a ton of sequence scans:

 Hash Join  (cost=113773.34..326420199.08 rows=1187847 width=46)
   Hash Cond: (a.uuid = myt.uuid)
   ->  Append  (cost=0.00..282743385.62 rows=11613643108 width=42)
         ->  Seq Scan on myt_2015_05 myt_4  (cost=0.00..7647300.32
rows=395295232 width=42)
         ->  Seq Scan on myt_2015_06 myt_5  (cost=0.00..7233480.44
rows=373904544 width=42)
         ->  Seq Scan on myt_2015_07 myt_6  (cost=0.00..6679997.60
rows=345294560 width=42)
         ->  Seq Scan on myt_2015_08 myt_7  (cost=0.00..7426294.64
rows=383871264 width=42)
         ->  Seq Scan on myt_2015_09 myt_8  (cost=0.00..7454691.04
rows=385339104 width=42)

Etc, all the way up to present. I then tried adding a btree index on the
uuid itself, same result.

Selecting directly on the table where the uuid = some value will use the
index, but joining to the table will NOT.

I ran analyze on the primary table, still a sequence scan.

This table is useless without an index on the uuid, what might be the
issue? Might my partitions be too large? Something else?

-- 
Wells Oliver
[email protected] <[email protected]>

--0000000000005e1933059c08b88c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi all. I have a table with 7 columns: uuid, date, sm=
allint, smallint, real, real, real.</div><div><br></div><div>The PK is the =
uuid, date, smallint, smallint.</div><div><br></div><div>It has 60 partitio=
ns on date, partitioned by month, back to 2015. Each partition is roughly 4=
0GB, about 350-450m rows.</div><div><br></div><div>Doing a join to this tab=
le using the uuid is not using an index, so it&#39;s never completing. So w=
here this table is b: SELECT * FROM a JOIN b ON A.uuid =3D B.uuid - I thoug=
ht it might use the PK as the uuid is the first column, but we end up with =
a ton of sequence scans:</div><div><br></div><div>=C2=A0Hash Join =C2=A0(co=
st=3D113773.34..326420199.08 rows=3D1187847 width=3D46)<br>=C2=A0 =C2=A0Has=
h Cond: (a.uuid =3D myt.uuid)<br>=C2=A0 =C2=A0-&gt; =C2=A0Append =C2=A0(cos=
t=3D0.00..282743385.62 rows=3D11613643108 width=3D42)<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on myt_2015_05 myt_4 =C2=A0(cost=3D0.=
00..7647300.32 rows=3D395295232 width=3D42)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0-&gt; =C2=A0Seq Scan on myt_2015_06 myt_5 =C2=A0(cost=3D0.00..7233480=
.44 rows=3D373904544 width=3D42)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt;=
 =C2=A0Seq Scan on myt_2015_07 myt_6 =C2=A0(cost=3D0.00..6679997.60 rows=3D=
345294560 width=3D42)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq =
Scan on myt_2015_08 myt_7 =C2=A0(cost=3D0.00..7426294.64 rows=3D383871264 w=
idth=3D42)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on myt=
_2015_09 myt_8 =C2=A0(cost=3D0.00..7454691.04 rows=3D385339104 width=3D42)<=
/div><div><br></div><div>Etc, all the way up to present. I then tried addin=
g a btree index on the uuid itself, same result.</div><div><br></div><div>S=
electing directly on the table where the uuid =3D some value will use the i=
ndex, but joining to the table will NOT.<br></div><div><br></div><div>I ran=
 analyze on the primary table, still a sequence scan.</div><div><br></div><=
div>This table is useless without an index on the uuid, what might be the i=
ssue? Might my partitions be too large? Something else?<br></div><div><br>-=
- <br><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_si=
gnature"><div dir=3D"ltr"><div>Wells Oliver<br><a href=3D"mailto:wellsolive=
[email protected]" target=3D"_blank">[email protected]</a></div></div></div>=
</div></div>

--0000000000005e1933059c08b88c--