pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDate vs Timestamp without timezone Partition Key
7+ messages / 3 participants
[nested] [flat]
* Date vs Timestamp without timezone Partition Key
@ 2020-05-25 03:50 Cedric Leong <cedricleong@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Cedric Leong @ 2020-05-25 03:50 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Just in case someone is interested enough to answer this. Does anyone know
if the performance for a date column vs a timestamp column as a partition
key is large? What i mean with large is that say you have 6 partitions with
10GB each. Would it be a 10 second+ difference? An explanation of how this
internally works would be appreciated.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date vs Timestamp without timezone Partition Key
@ 2020-05-25 04:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Cedric Leong <cedricleong@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2020-05-25 04:48 UTC (permalink / raw)
To: Cedric Leong <cedricleong@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Cedric Leong <cedricleong@gmail.com> writes:
> Just in case someone is interested enough to answer this. Does anyone know
> if the performance for a date column vs a timestamp column as a partition
> key is large?
I doubt it's even measurable, at least on 64-bit machines. You're
basically talking about 32-bit integer comparisons vs 64-bit integer
comparisons.
On a 32-bit machine it's possible that an index on a date column
will be physically smaller, so you could get some wins from reduced
I/O. But on (most?) 64-bit machines that difference goes away too,
because of alignment restrictions.
As always, YMMV; it never hurts to do your own testing.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date vs Timestamp without timezone Partition Key
@ 2020-06-06 02:12 Cedric Leong <cedricleong@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Cedric Leong @ 2020-06-06 02:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Somewhat unrelated but note to anyone who wants to swap out partition keys.
Don't create a clone of the table with the new partition key and insert
data. It messes up the query planner massively and makes everything much
slower.
On Mon, May 25, 2020 at 12:48 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Cedric Leong <cedricleong@gmail.com> writes:
> > Just in case someone is interested enough to answer this. Does anyone
> know
> > if the performance for a date column vs a timestamp column as a partition
> > key is large?
>
> I doubt it's even measurable, at least on 64-bit machines. You're
> basically talking about 32-bit integer comparisons vs 64-bit integer
> comparisons.
>
> On a 32-bit machine it's possible that an index on a date column
> will be physically smaller, so you could get some wins from reduced
> I/O. But on (most?) 64-bit machines that difference goes away too,
> because of alignment restrictions.
>
> As always, YMMV; it never hurts to do your own testing.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date vs Timestamp without timezone Partition Key
@ 2020-06-06 02:16 David Rowley <dgrowleyml@gmail.com>
parent: Cedric Leong <cedricleong@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2020-06-06 02:16 UTC (permalink / raw)
To: Cedric Leong <cedricleong@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sat, 6 Jun 2020 at 14:12, Cedric Leong <cedricleong@gmail.com> wrote:
> Somewhat unrelated but note to anyone who wants to swap out partition keys. Don't create a clone of the table with the new partition key and insert data. It messes up the query planner massively and makes everything much slower.
That complaint would have more meaning if you'd mentioned which
version of PostgreSQL you're using. The performance of partitioning in
PostgreSQL has changed significantly over the past 3 releases. Also
would be useful to know what you've actually done (actual commands).
I can't imagine it makes *everything* slower, so it might be good to
mention what is actually slower.
David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date vs Timestamp without timezone Partition Key
@ 2020-06-06 02:49 Cedric Leong <cedricleong@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Cedric Leong @ 2020-06-06 02:49 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
It's less of a complaint rather than just a warning not to do what I did.
Version:
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
The actual command list would probably be impractical to put in here just
because the majority of it would just be creating a large amount of
partition tables. But in summary what i've done is basically this:
Existing database has a partitioned fact table
1. Create an exact clone of that partitioned fact table which includes all
the same indexes, columns, and partitioned tables
2. Change the partitioned table's partition key from an indexed date column
to an indexed timestamp without timezone column
3. Do an insert into from the old partitioned fact table to the new
partitioned fact table which includes all the same rows (insert into since
i wanted the timestamp without timezone column to be in a new timezone)
4. Switch the names of the tables so the new one will be the one that's used
5. VACUUM FULL; ANALYZE;
For my use case which is a data warehouse star schema, this fact table is
basically the base table of every report. To be more specific, the reports
I've tested on varied from 2x slower to 4x slower. From what I see so far
that's because the query plan is drastically different for both. An example
of this test would look like this: https://explain.depesz.com/s/6rP8 and
https://explain.depesz.com/s/cLUY
These tests are running the exact same query on two different tables with
the exception that they use their respective partition keys.
On Fri, Jun 5, 2020 at 10:17 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Sat, 6 Jun 2020 at 14:12, Cedric Leong <cedricleong@gmail.com> wrote:
> > Somewhat unrelated but note to anyone who wants to swap out partition
> keys. Don't create a clone of the table with the new partition key and
> insert data. It messes up the query planner massively and makes everything
> much slower.
>
> That complaint would have more meaning if you'd mentioned which
> version of PostgreSQL you're using. The performance of partitioning in
> PostgreSQL has changed significantly over the past 3 releases. Also
> would be useful to know what you've actually done (actual commands).
> I can't imagine it makes *everything* slower, so it might be good to
> mention what is actually slower.
>
> David
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date vs Timestamp without timezone Partition Key
@ 2020-06-06 03:13 David Rowley <dgrowleyml@gmail.com>
parent: Cedric Leong <cedricleong@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2020-06-06 03:13 UTC (permalink / raw)
To: Cedric Leong <cedricleong@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sat, 6 Jun 2020 at 14:49, Cedric Leong <cedricleong@gmail.com> wrote:
> It's less of a complaint rather than just a warning not to do what I did.
My point was really that nobody really knew what you did or what you
did it on. So it didn't seem like a worthwhile warning as it
completely lacked detail.
> These tests are running the exact same query on two different tables with the exception that they use their respective partition keys.
Are you sure? It looks like the old one does WHERE date =
((now())::date - '7 days'::interval) and the new version does
(date(created_at) = ((now())::date - '7 days'::interval). I guess you
renamed date to "created_at" and changed the query to use date(). If
that expression is not indexed then I imagine that would be a good
reason for the planner to have moved away from using the index on that
column. Also having date(created_at) will also not allow run-time
pruning to work since your partition key is "created_at".
You might be able to change the query to query a range of value on the
new timestamp column. This will allow you to get rid of the date()
function. For example:
where created_at >= date_trunc('day', now() - '7 days'::interval) and
created_at < date_trunc('day', now() - '6 days'::interval)
David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Date vs Timestamp without timezone Partition Key
@ 2020-06-06 03:56 Cedric Leong <cedricleong@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Cedric Leong @ 2020-06-06 03:56 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
I can confirm that was the issue, after removing the expression and using
only what was indexed it definitely fixed the query plan. I appreciate all
the help you've given me, I didn't really think to look there but it makes
a ton of sense that a filter on the database would only work well if it's
indexed.
Thanks again,
On Fri, Jun 5, 2020 at 11:13 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Sat, 6 Jun 2020 at 14:49, Cedric Leong <cedricleong@gmail.com> wrote:
> > It's less of a complaint rather than just a warning not to do what I did.
>
> My point was really that nobody really knew what you did or what you
> did it on. So it didn't seem like a worthwhile warning as it
> completely lacked detail.
>
> > These tests are running the exact same query on two different tables
> with the exception that they use their respective partition keys.
>
> Are you sure? It looks like the old one does WHERE date =
> ((now())::date - '7 days'::interval) and the new version does
> (date(created_at) = ((now())::date - '7 days'::interval). I guess you
> renamed date to "created_at" and changed the query to use date(). If
> that expression is not indexed then I imagine that would be a good
> reason for the planner to have moved away from using the index on that
> column. Also having date(created_at) will also not allow run-time
> pruning to work since your partition key is "created_at".
>
> You might be able to change the query to query a range of value on the
> new timestamp column. This will allow you to get rid of the date()
> function. For example:
>
> where created_at >= date_trunc('day', now() - '7 days'::interval) and
> created_at < date_trunc('day', now() - '6 days'::interval)
>
> David
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-06-06 03:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-25 03:50 Date vs Timestamp without timezone Partition Key Cedric Leong <cedricleong@gmail.com>
2020-05-25 04:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-06-06 02:12 ` Cedric Leong <cedricleong@gmail.com>
2020-06-06 02:16 ` David Rowley <dgrowleyml@gmail.com>
2020-06-06 02:49 ` Cedric Leong <cedricleong@gmail.com>
2020-06-06 03:13 ` David Rowley <dgrowleyml@gmail.com>
2020-06-06 03:56 ` Cedric Leong <cedricleong@gmail.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