Re: Declarative partitioning, UUIDs, index issues.
MichaelDBA <[email protected]> Mon, 13 Jan 2020 15:41:00 -0500
| Newsgroups | gmane.comp.db.postgresql.admin |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --------------FEDAAE479BEDAE076FC30DDA Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi Oliver, I know I am not addressing your immediate concern about why it is not using the index, but I have another question first: You are using a query that is not doing any partition exclusion since you are not providing the date on the where clause, which is the partitioning key, right? If so, I would expect to see all the partitions in the explain output, but I only see 4 thru 8. Regards, Michael Vitale Wells Oliver wrote on 1/13/2020 12:21 PM: > 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] <mailto:[email protected]> --------------FEDAAE479BEDAE076FC30DDA Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit <html theme="default-light"><head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> </head><body text="#000000">Hi Oliver,<br> <br> I know I am not addressing your immediate concern about why it is not using the index, but I have another question first:<br> <br> You are using a query that is not doing any partition exclusion since you are not providing the date on the where clause, which is the partitioning key, right? If so, I would expect to see all the partitions in the explain output, but I only see 4 thru 8.<br> <br> Regards,<br> Michael Vitale<br> <br> <span>Wells Oliver wrote on 1/13/2020 12:21 PM:</span><br> <blockquote type="cite" cite="mid:CAOC+FBXcPFFF=e=OUYnPSZc+_6j=TTAMFceQhKRPUjJR9dmvgQ@mail.gmail.com"> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <div dir="ltr"><div>Hi all. I have a table with 7 columns: uuid, date, smallint, 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 partitions on date, partitioned by month, back to 2015. Each partition is roughly 40GB, about 350-450m rows.</div><div><br></div><div>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:</div><div><br></div><div> Hash Join (cost=113773.34..326420199.08 rows=1187847 width=46)<br> Hash Cond: (a.uuid = myt.uuid)<br> -> Append (cost=0.00..282743385.62 rows=11613643108 width=42)<br> -> Seq Scan on myt_2015_05 myt_4 (cost=0.00..7647300.32 rows=395295232 width=42)<br> -> Seq Scan on myt_2015_06 myt_5 (cost=0.00..7233480.44 rows=373904544 width=42)<br> -> Seq Scan on myt_2015_07 myt_6 (cost=0.00..6679997.60 rows=345294560 width=42)<br> -> Seq Scan on myt_2015_08 myt_7 (cost=0.00..7426294.64 rows=383871264 width=42)<br> -> Seq Scan on myt_2015_09 myt_8 (cost=0.00..7454691.04 rows=385339104 width=42)</div><div><br></div><div>Etc, all the way up to present. I then tried adding a btree index on the uuid itself, same result.</div><div><br></div><div>Selecting directly on the table where the uuid = some value will use the index, 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 issue? Might my partitions be too large? Something else?<br></div><div><br>-- <br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div>Wells Oliver<br><a href="mailto:[email protected]" target="_blank" moz-do-not-send="true">[email protected]</a></div></div></div></div></div> </blockquote> <br> </body></html> --------------FEDAAE479BEDAE076FC30DDA--