Re: Declarative partitioning, UUIDs, index issues.

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
wells.oliver@gmail.com