Subject: Re: Declarative partitioning, UUIDs, index issues.
To: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
References: 
 <CAOC+FBXcPFFF=e=OUYnPSZc+_6j=TTAMFceQhKRPUjJR9dmvgQ@mail.gmail.com>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <96ab1ca0-c527-8ba9-dedc-5cfe90eecc1e@sqlexec.com>
Date: Mon, 13 Jan 2020 15:41:00 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.10
MIME-Version: 1.0
In-Reply-To: 
 <CAOC+FBXcPFFF=e=OUYnPSZc+_6j=TTAMFceQhKRPUjJR9dmvgQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------FEDAAE479BEDAE076FC30DDA"
Content-Language: en-US
Precedence: bulk

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


--------------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>   -&gt;  Append  (cost=0.00..282743385.62 
rows=11613643108 width=42)<br>         -&gt;  Seq Scan on myt_2015_05 
myt_4  (cost=0.00..7647300.32 rows=395295232 width=42)<br>         -&gt;
  Seq Scan on myt_2015_06 myt_5  (cost=0.00..7233480.44 rows=373904544 
width=42)<br>         -&gt;  Seq Scan on myt_2015_07 myt_6 
 (cost=0.00..6679997.60 rows=345294560 width=42)<br>         -&gt;  Seq 
Scan on myt_2015_08 myt_7  (cost=0.00..7426294.64 rows=383871264 
width=42)<br>         -&gt;  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:wellsoliver@gmail.com" 
target="_blank" moz-do-not-send="true">wells.oliver@gmail.com</a></div></div></div></div></div>
</blockquote>
<br>
</body></html>

--------------FEDAAE479BEDAE076FC30DDA--