Re: Date vs Timestamp without timezone Partition Key

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Cedric Leong <cedricleong(at)gmail(dot)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
>