From: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Declarative partitioning, UUIDs, index issues. |
Date: | 2020-01-13 17:21:54 |
Message-ID: | CAOC+FBXcPFFF=e=OUYnPSZc+_6j=TTAMFceQhKRPUjJR9dmvgQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | MichaelDBA | 2020-01-13 20:41:00 | Re: Declarative partitioning, UUIDs, index issues. |
Previous Message | robert | 2020-01-12 11:41:44 | Re: container restarted -> postgresql dead but pid file exists |