Re: Slow PostgreSQL 10.6 query
Thanks for the tip! Regards, Behrang (sent from my mobile) On Mon, Oct 7, 2019, 07:37 Tomas Vondra wrote: > On Tue, Oct 01, 2019 at 11:42:33PM +1000, Behrang Saeedzadeh wrote: > >Thanks. That eliminated the bottleneck! > > > >Any ideas why adding ORDER BY to the subquery also changes the plan in a > >way that eliminates the bottleneck? > > > > IIRC the ORDER BY clause makes it impossible to "collapse" the subquery > into the main (upper) one, and it probably happens to constrict the > choices so that the planner ends up picking a good plan. I guess adding > "OFFSET 0" to the subquery would have the same effect. > > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: distinct on extract returns composite type
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
