Re: how to "explain" some ddl

Hi,
thanks for the answer.
the pb is that the fact table do have mods for "old" data.
so the current scheme implies to truncate partitions and recreate them, and
copy from ods to dm, etc which is better than millions (tens of) delete and
vacuuming.
and so, the partitioning scheme is based on day s data. so the 1800+.

the other pb we do have is the very long planning time for most request.
was 120 sec in r11, down to 60 sec in 12.
vs an exec time around 4 sec. Looks like the number of indexes is of
paramount impact.
Can you take me to any doc about optimizing the index scheme for a fact
table with 40 dimensions ?
thanks
regards,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, Jul 14, 2020 at 7:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Marc Millas <marc(dot)millas(at)mokadb(dot)com> writes:
> > We would like to understand where an alter table attach partition spend
> its
> > time.
> > to my understanding, explain doesnt do this.
>
> Nope :-(. As our DDL commands have gotten more complicated, there's
> been some discussion of adding that, but nothing's really been done
> yet.
>
> There is some progress-monitoring support for some DDL commands now,
> but that's not quite the same thing.
>
> > for a BI job we have a partitionned table with 1800+ partitions.
>
> TBH I'd recommend scaling that down by at least a factor of ten.
> We are not at a point where you can expect that all operations will
> be cheap even with thousands of partitions. We may never be at that
> point, although people continue to chip away at the bottlenecks.
>
> regards, tom lane
>