| From: | Michael Lewis <mlewis(at)entrata(dot)com> |
|---|---|
| To: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Félix GERZAGUET <felix(dot)gerzaguet(at)gmail(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org |
| Subject: | Re: distinct on extract returns composite type |
| Date: | 2019-10-07 21:53:17 |
| Message-ID: | CAHOFxGpB=Oht61x5GatDDDJNzZKwnezS34FmfqFrvff67pT8sQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
As long as we are on the performance list and not general, it might be
worth noting that partitioning should be defined directly on the data and
not on a function result I believe. If you always do the extract year and
extract quarter thing, it may work out just fine. But just a regular btree
index on the date/timestamp/timestamptz field and partitions like the below
might be much easier to work with.
MINVALUE to 2018-01-01 /* the top end is always exclusive so it gets
referenced as top on this partition and start of the next partition */
2018-01-01 to 2018-04-01
2018-04-01 to 2018-07-01
2018-07-01 to 2018-10-01
2018-10-01 to 2019-01-01
2019-01-01 to 2019-04-01
2019-04-01 to 2019-07-01
2019-07-01 to 2019-10-01
2019-10-01 to 2020-01-01
2020-01-01 to MAXVALUE
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Merlin Moncure | 2019-10-08 17:44:39 | Re: Out of Memory errors are frustrating as heck! |
| Previous Message | Behrang Saeedzadeh | 2019-10-07 08:27:42 | Re: Slow PostgreSQL 10.6 query |