pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDeclarative partitioning, UUIDs, index issues.
2+ messages / 2 participants
[nested] [flat]
* Declarative partitioning, UUIDs, index issues.
@ 2020-01-13 17:21 Wells Oliver <wells.oliver@gmail.com>
2020-01-13 20:41 ` Re: Declarative partitioning, UUIDs, index issues. MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 2+ messages in thread
From: Wells Oliver @ 2020-01-13 17:21 UTC (permalink / raw)
To: 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.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Declarative partitioning, UUIDs, index issues.
2020-01-13 17:21 Declarative partitioning, UUIDs, index issues. Wells Oliver <wells.oliver@gmail.com>
@ 2020-01-13 20:41 ` MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 2+ messages in thread
From: MichaelDBA @ 2020-01-13 20:41 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-01-13 20:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-13 17:21 Declarative partitioning, UUIDs, index issues. Wells Oliver <wells.oliver@gmail.com>
2020-01-13 20:41 ` MichaelDBA <MichaelDBA@sqlexec.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox