You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hawq.apache.org by Ming Li <ml...@pivotal.io> on 2016/07/02 15:19:10 UTC
[Propose] More data skipping technology for IO intensive performance enhancement
Data skipping technology can extremely avoiding unnecessary IO, so it can
extremely enhance performance for IO intensive query. Including eliminating
query on unnecessary table partition according to the partition key range ,
I think more options are available now:
(1) Parquet / ORC format introduce a lightweight meta data info like
Min/Max/Bloom filter for each block, such meta data can be exploited when
predicate/filter info can be fetched before executing scan.
However now in HAWQ, all data in parquet need to be scanned into memory
before processing predicate/filter. We don't generate the meta info when
INSERT into parquet table, the scan executor doesn't utilize the meta info
neither. Maybe some scan API need to be refactored so that we can get
predicate/filter
info before executing base relation scan.
(2) Base on (1) technology, especially with Bloom filter, more optimizer
technology can be explored furthur. E.g. Impala implemented Runtime
filtering(*https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
<https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html>*
), which can be used at
- dynamic partition pruning
- converting join predicate to base relation predicate
It tell the executor to wait for one moment(the interval time can be set in
guc) before executing base relation scan, if the interested values(e.g. the
column in join predicate only have very small set) arrived in time, it can
use these value to filter this scan, if doesn't arrived in time, it scan
without this filter, which doesn't impact result correctness.
Unlike (1) technology, this technology cannot be used in any case, it only
outperform in some cases. So it just add some more query plan
choices/paths, and the optimizer need based on statistics info to calculate
the cost, and apply it when cost down.
All in one, maybe more similar technology can be adoptable for HAWQ now,
let's start to think about performance related technology, moreover we need
to instigate how these technology can be implemented in HAWQ.
Any ideas or suggestions are welcomed? Thanks.
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Lei Chang <le...@apache.org>.
looks we get consensus on this enhancement, i just started a JIRA to track
this: https://issues.apache.org/jira/browse/HAWQ-923
and I also added this to performance enhancement on roadmap page
Cheers
Lei
On Mon, Jul 11, 2016 at 2:00 PM, Ming Li <ml...@pivotal.io> wrote:
> It seems the Dynamic partition pruning in impala is different from the DPE
> (dynamic partition elimination) in HAWQ, below is the feature description
> from impala roadmap (http://impala.io/overview.html).
>
>
> - Dynamic partition pruning - to perform data elimination of queries
> where the partition filters are in dimension tables instead of the fact
> tables
>
>
> On Fri, Jul 8, 2016 at 9:56 PM, Ruilong Huo <rh...@pivotal.io> wrote:
>
> > Strong agree with Ming's proposal.
> >
> > We do have DPE (dynamic partition elimination) in HAWQ. But it is a kind
> of
> > high level skipping which is conducted at planning phase.
> > If fine-grained filter can be done at runtime in execution phase, there
> > might be more performance gain for I/O intensive workload.
> >
> > Looking forward to see a plan for it soon:)
> >
> > Best regards,
> > Ruilong Huo
> >
> > On Fri, Jul 8, 2016 at 7:02 AM, Ivan Weng <iw...@pivotal.io> wrote:
> >
> > > Thanks Ming, data skipping technology is really what HAWQ needed.
> > > Hope to see this design and maybe prototype soon.
> > >
> > > On Thu, Jul 7, 2016 at 10:33 AM, Wen Lin <wl...@pivotal.io> wrote:
> > >
> > > > Thanks for sharing with us!
> > > > It's really a good investigation and proposal.
> > > > Iooking forward to a design draft.
> > > >
> > > > On Thu, Jul 7, 2016 at 10:16 AM, Lili Ma <lm...@pivotal.io> wrote:
> > > >
> > > > > What about we work out a draft design describing how to implement
> > data
> > > > > skipping technology for HAWQ?
> > > > >
> > > > >
> > > > > Thanks
> > > > > Lili
> > > > >
> > > > > On Wed, Jul 6, 2016 at 7:23 PM, Gmail <xu...@gmail.com>
> wrote:
> > > > >
> > > > > > BTW, could you create some related issues in JIRA?
> > > > > >
> > > > > > Thanks
> > > > > > xunzhang
> > > > > >
> > > > > > Send from my iPhone
> > > > > >
> > > > > > > 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
> > > > > > >
> > > > > > > Data skipping technology can extremely avoiding unnecessary IO,
> > so
> > > > it
> > > > > > can
> > > > > > > extremely enhance performance for IO intensive query. Including
> > > > > > eliminating
> > > > > > > query on unnecessary table partition according to the partition
> > key
> > > > > > range ,
> > > > > > > I think more options are available now:
> > > > > > >
> > > > > > > (1) Parquet / ORC format introduce a lightweight meta data info
> > > like
> > > > > > > Min/Max/Bloom filter for each block, such meta data can be
> > > exploited
> > > > > when
> > > > > > > predicate/filter info can be fetched before executing scan.
> > > > > > >
> > > > > > > However now in HAWQ, all data in parquet need to be scanned
> into
> > > > memory
> > > > > > > before processing predicate/filter. We don't generate the meta
> > info
> > > > > when
> > > > > > > INSERT into parquet table, the scan executor doesn't utilize
> the
> > > meta
> > > > > > info
> > > > > > > neither. Maybe some scan API need to be refactored so that we
> can
> > > get
> > > > > > > predicate/filter
> > > > > > > info before executing base relation scan.
> > > > > > >
> > > > > > > (2) Base on (1) technology, especially with Bloom filter, more
> > > > > optimizer
> > > > > > > technology can be explored furthur. E.g. Impala implemented
> > Runtime
> > > > > > > filtering(*
> > > > > >
> > > > >
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > > > > <
> > > > > >
> > > > >
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > > > >*
> > > > > > > ), which can be used at
> > > > > > > - dynamic partition pruning
> > > > > > > - converting join predicate to base relation predicate
> > > > > > >
> > > > > > > It tell the executor to wait for one moment(the interval time
> can
> > > be
> > > > > set
> > > > > > in
> > > > > > > guc) before executing base relation scan, if the interested
> > > > values(e.g.
> > > > > > the
> > > > > > > column in join predicate only have very small set) arrived in
> > time,
> > > > it
> > > > > > can
> > > > > > > use these value to filter this scan, if doesn't arrived in
> time,
> > it
> > > > > scan
> > > > > > > without this filter, which doesn't impact result correctness.
> > > > > > >
> > > > > > > Unlike (1) technology, this technology cannot be used in any
> > case,
> > > it
> > > > > > only
> > > > > > > outperform in some cases. So it just add some more query plan
> > > > > > > choices/paths, and the optimizer need based on statistics info
> to
> > > > > > calculate
> > > > > > > the cost, and apply it when cost down.
> > > > > > >
> > > > > > > All in one, maybe more similar technology can be adoptable for
> > HAWQ
> > > > > now,
> > > > > > > let's start to think about performance related technology,
> > moreover
> > > > we
> > > > > > need
> > > > > > > to instigate how these technology can be implemented in HAWQ.
> > > > > > >
> > > > > > > Any ideas or suggestions are welcomed? Thanks.
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Ming Li <ml...@pivotal.io>.
It seems the Dynamic partition pruning in impala is different from the DPE
(dynamic partition elimination) in HAWQ, below is the feature description
from impala roadmap (http://impala.io/overview.html).
- Dynamic partition pruning - to perform data elimination of queries
where the partition filters are in dimension tables instead of the fact
tables
On Fri, Jul 8, 2016 at 9:56 PM, Ruilong Huo <rh...@pivotal.io> wrote:
> Strong agree with Ming's proposal.
>
> We do have DPE (dynamic partition elimination) in HAWQ. But it is a kind of
> high level skipping which is conducted at planning phase.
> If fine-grained filter can be done at runtime in execution phase, there
> might be more performance gain for I/O intensive workload.
>
> Looking forward to see a plan for it soon:)
>
> Best regards,
> Ruilong Huo
>
> On Fri, Jul 8, 2016 at 7:02 AM, Ivan Weng <iw...@pivotal.io> wrote:
>
> > Thanks Ming, data skipping technology is really what HAWQ needed.
> > Hope to see this design and maybe prototype soon.
> >
> > On Thu, Jul 7, 2016 at 10:33 AM, Wen Lin <wl...@pivotal.io> wrote:
> >
> > > Thanks for sharing with us!
> > > It's really a good investigation and proposal.
> > > Iooking forward to a design draft.
> > >
> > > On Thu, Jul 7, 2016 at 10:16 AM, Lili Ma <lm...@pivotal.io> wrote:
> > >
> > > > What about we work out a draft design describing how to implement
> data
> > > > skipping technology for HAWQ?
> > > >
> > > >
> > > > Thanks
> > > > Lili
> > > >
> > > > On Wed, Jul 6, 2016 at 7:23 PM, Gmail <xu...@gmail.com> wrote:
> > > >
> > > > > BTW, could you create some related issues in JIRA?
> > > > >
> > > > > Thanks
> > > > > xunzhang
> > > > >
> > > > > Send from my iPhone
> > > > >
> > > > > > 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
> > > > > >
> > > > > > Data skipping technology can extremely avoiding unnecessary IO,
> so
> > > it
> > > > > can
> > > > > > extremely enhance performance for IO intensive query. Including
> > > > > eliminating
> > > > > > query on unnecessary table partition according to the partition
> key
> > > > > range ,
> > > > > > I think more options are available now:
> > > > > >
> > > > > > (1) Parquet / ORC format introduce a lightweight meta data info
> > like
> > > > > > Min/Max/Bloom filter for each block, such meta data can be
> > exploited
> > > > when
> > > > > > predicate/filter info can be fetched before executing scan.
> > > > > >
> > > > > > However now in HAWQ, all data in parquet need to be scanned into
> > > memory
> > > > > > before processing predicate/filter. We don't generate the meta
> info
> > > > when
> > > > > > INSERT into parquet table, the scan executor doesn't utilize the
> > meta
> > > > > info
> > > > > > neither. Maybe some scan API need to be refactored so that we can
> > get
> > > > > > predicate/filter
> > > > > > info before executing base relation scan.
> > > > > >
> > > > > > (2) Base on (1) technology, especially with Bloom filter, more
> > > > optimizer
> > > > > > technology can be explored furthur. E.g. Impala implemented
> Runtime
> > > > > > filtering(*
> > > > >
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > > > <
> > > > >
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > > >*
> > > > > > ), which can be used at
> > > > > > - dynamic partition pruning
> > > > > > - converting join predicate to base relation predicate
> > > > > >
> > > > > > It tell the executor to wait for one moment(the interval time can
> > be
> > > > set
> > > > > in
> > > > > > guc) before executing base relation scan, if the interested
> > > values(e.g.
> > > > > the
> > > > > > column in join predicate only have very small set) arrived in
> time,
> > > it
> > > > > can
> > > > > > use these value to filter this scan, if doesn't arrived in time,
> it
> > > > scan
> > > > > > without this filter, which doesn't impact result correctness.
> > > > > >
> > > > > > Unlike (1) technology, this technology cannot be used in any
> case,
> > it
> > > > > only
> > > > > > outperform in some cases. So it just add some more query plan
> > > > > > choices/paths, and the optimizer need based on statistics info to
> > > > > calculate
> > > > > > the cost, and apply it when cost down.
> > > > > >
> > > > > > All in one, maybe more similar technology can be adoptable for
> HAWQ
> > > > now,
> > > > > > let's start to think about performance related technology,
> moreover
> > > we
> > > > > need
> > > > > > to instigate how these technology can be implemented in HAWQ.
> > > > > >
> > > > > > Any ideas or suggestions are welcomed? Thanks.
> > > > >
> > > >
> > >
> >
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Ruilong Huo <rh...@pivotal.io>.
Strong agree with Ming's proposal.
We do have DPE (dynamic partition elimination) in HAWQ. But it is a kind of
high level skipping which is conducted at planning phase.
If fine-grained filter can be done at runtime in execution phase, there
might be more performance gain for I/O intensive workload.
Looking forward to see a plan for it soon:)
Best regards,
Ruilong Huo
On Fri, Jul 8, 2016 at 7:02 AM, Ivan Weng <iw...@pivotal.io> wrote:
> Thanks Ming, data skipping technology is really what HAWQ needed.
> Hope to see this design and maybe prototype soon.
>
> On Thu, Jul 7, 2016 at 10:33 AM, Wen Lin <wl...@pivotal.io> wrote:
>
> > Thanks for sharing with us!
> > It's really a good investigation and proposal.
> > Iooking forward to a design draft.
> >
> > On Thu, Jul 7, 2016 at 10:16 AM, Lili Ma <lm...@pivotal.io> wrote:
> >
> > > What about we work out a draft design describing how to implement data
> > > skipping technology for HAWQ?
> > >
> > >
> > > Thanks
> > > Lili
> > >
> > > On Wed, Jul 6, 2016 at 7:23 PM, Gmail <xu...@gmail.com> wrote:
> > >
> > > > BTW, could you create some related issues in JIRA?
> > > >
> > > > Thanks
> > > > xunzhang
> > > >
> > > > Send from my iPhone
> > > >
> > > > > 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
> > > > >
> > > > > Data skipping technology can extremely avoiding unnecessary IO, so
> > it
> > > > can
> > > > > extremely enhance performance for IO intensive query. Including
> > > > eliminating
> > > > > query on unnecessary table partition according to the partition key
> > > > range ,
> > > > > I think more options are available now:
> > > > >
> > > > > (1) Parquet / ORC format introduce a lightweight meta data info
> like
> > > > > Min/Max/Bloom filter for each block, such meta data can be
> exploited
> > > when
> > > > > predicate/filter info can be fetched before executing scan.
> > > > >
> > > > > However now in HAWQ, all data in parquet need to be scanned into
> > memory
> > > > > before processing predicate/filter. We don't generate the meta info
> > > when
> > > > > INSERT into parquet table, the scan executor doesn't utilize the
> meta
> > > > info
> > > > > neither. Maybe some scan API need to be refactored so that we can
> get
> > > > > predicate/filter
> > > > > info before executing base relation scan.
> > > > >
> > > > > (2) Base on (1) technology, especially with Bloom filter, more
> > > optimizer
> > > > > technology can be explored furthur. E.g. Impala implemented Runtime
> > > > > filtering(*
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > > <
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > >*
> > > > > ), which can be used at
> > > > > - dynamic partition pruning
> > > > > - converting join predicate to base relation predicate
> > > > >
> > > > > It tell the executor to wait for one moment(the interval time can
> be
> > > set
> > > > in
> > > > > guc) before executing base relation scan, if the interested
> > values(e.g.
> > > > the
> > > > > column in join predicate only have very small set) arrived in time,
> > it
> > > > can
> > > > > use these value to filter this scan, if doesn't arrived in time, it
> > > scan
> > > > > without this filter, which doesn't impact result correctness.
> > > > >
> > > > > Unlike (1) technology, this technology cannot be used in any case,
> it
> > > > only
> > > > > outperform in some cases. So it just add some more query plan
> > > > > choices/paths, and the optimizer need based on statistics info to
> > > > calculate
> > > > > the cost, and apply it when cost down.
> > > > >
> > > > > All in one, maybe more similar technology can be adoptable for HAWQ
> > > now,
> > > > > let's start to think about performance related technology, moreover
> > we
> > > > need
> > > > > to instigate how these technology can be implemented in HAWQ.
> > > > >
> > > > > Any ideas or suggestions are welcomed? Thanks.
> > > >
> > >
> >
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Ivan Weng <iw...@pivotal.io>.
Thanks Ming, data skipping technology is really what HAWQ needed.
Hope to see this design and maybe prototype soon.
On Thu, Jul 7, 2016 at 10:33 AM, Wen Lin <wl...@pivotal.io> wrote:
> Thanks for sharing with us!
> It's really a good investigation and proposal.
> Iooking forward to a design draft.
>
> On Thu, Jul 7, 2016 at 10:16 AM, Lili Ma <lm...@pivotal.io> wrote:
>
> > What about we work out a draft design describing how to implement data
> > skipping technology for HAWQ?
> >
> >
> > Thanks
> > Lili
> >
> > On Wed, Jul 6, 2016 at 7:23 PM, Gmail <xu...@gmail.com> wrote:
> >
> > > BTW, could you create some related issues in JIRA?
> > >
> > > Thanks
> > > xunzhang
> > >
> > > Send from my iPhone
> > >
> > > > 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
> > > >
> > > > Data skipping technology can extremely avoiding unnecessary IO, so
> it
> > > can
> > > > extremely enhance performance for IO intensive query. Including
> > > eliminating
> > > > query on unnecessary table partition according to the partition key
> > > range ,
> > > > I think more options are available now:
> > > >
> > > > (1) Parquet / ORC format introduce a lightweight meta data info like
> > > > Min/Max/Bloom filter for each block, such meta data can be exploited
> > when
> > > > predicate/filter info can be fetched before executing scan.
> > > >
> > > > However now in HAWQ, all data in parquet need to be scanned into
> memory
> > > > before processing predicate/filter. We don't generate the meta info
> > when
> > > > INSERT into parquet table, the scan executor doesn't utilize the meta
> > > info
> > > > neither. Maybe some scan API need to be refactored so that we can get
> > > > predicate/filter
> > > > info before executing base relation scan.
> > > >
> > > > (2) Base on (1) technology, especially with Bloom filter, more
> > optimizer
> > > > technology can be explored furthur. E.g. Impala implemented Runtime
> > > > filtering(*
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > <
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > >*
> > > > ), which can be used at
> > > > - dynamic partition pruning
> > > > - converting join predicate to base relation predicate
> > > >
> > > > It tell the executor to wait for one moment(the interval time can be
> > set
> > > in
> > > > guc) before executing base relation scan, if the interested
> values(e.g.
> > > the
> > > > column in join predicate only have very small set) arrived in time,
> it
> > > can
> > > > use these value to filter this scan, if doesn't arrived in time, it
> > scan
> > > > without this filter, which doesn't impact result correctness.
> > > >
> > > > Unlike (1) technology, this technology cannot be used in any case, it
> > > only
> > > > outperform in some cases. So it just add some more query plan
> > > > choices/paths, and the optimizer need based on statistics info to
> > > calculate
> > > > the cost, and apply it when cost down.
> > > >
> > > > All in one, maybe more similar technology can be adoptable for HAWQ
> > now,
> > > > let's start to think about performance related technology, moreover
> we
> > > need
> > > > to instigate how these technology can be implemented in HAWQ.
> > > >
> > > > Any ideas or suggestions are welcomed? Thanks.
> > >
> >
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Wen Lin <wl...@pivotal.io>.
Thanks for sharing with us!
It's really a good investigation and proposal.
Iooking forward to a design draft.
On Thu, Jul 7, 2016 at 10:16 AM, Lili Ma <lm...@pivotal.io> wrote:
> What about we work out a draft design describing how to implement data
> skipping technology for HAWQ?
>
>
> Thanks
> Lili
>
> On Wed, Jul 6, 2016 at 7:23 PM, Gmail <xu...@gmail.com> wrote:
>
> > BTW, could you create some related issues in JIRA?
> >
> > Thanks
> > xunzhang
> >
> > Send from my iPhone
> >
> > > 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
> > >
> > > Data skipping technology can extremely avoiding unnecessary IO, so it
> > can
> > > extremely enhance performance for IO intensive query. Including
> > eliminating
> > > query on unnecessary table partition according to the partition key
> > range ,
> > > I think more options are available now:
> > >
> > > (1) Parquet / ORC format introduce a lightweight meta data info like
> > > Min/Max/Bloom filter for each block, such meta data can be exploited
> when
> > > predicate/filter info can be fetched before executing scan.
> > >
> > > However now in HAWQ, all data in parquet need to be scanned into memory
> > > before processing predicate/filter. We don't generate the meta info
> when
> > > INSERT into parquet table, the scan executor doesn't utilize the meta
> > info
> > > neither. Maybe some scan API need to be refactored so that we can get
> > > predicate/filter
> > > info before executing base relation scan.
> > >
> > > (2) Base on (1) technology, especially with Bloom filter, more
> optimizer
> > > technology can be explored furthur. E.g. Impala implemented Runtime
> > > filtering(*
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > <
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > >*
> > > ), which can be used at
> > > - dynamic partition pruning
> > > - converting join predicate to base relation predicate
> > >
> > > It tell the executor to wait for one moment(the interval time can be
> set
> > in
> > > guc) before executing base relation scan, if the interested values(e.g.
> > the
> > > column in join predicate only have very small set) arrived in time, it
> > can
> > > use these value to filter this scan, if doesn't arrived in time, it
> scan
> > > without this filter, which doesn't impact result correctness.
> > >
> > > Unlike (1) technology, this technology cannot be used in any case, it
> > only
> > > outperform in some cases. So it just add some more query plan
> > > choices/paths, and the optimizer need based on statistics info to
> > calculate
> > > the cost, and apply it when cost down.
> > >
> > > All in one, maybe more similar technology can be adoptable for HAWQ
> now,
> > > let's start to think about performance related technology, moreover we
> > need
> > > to instigate how these technology can be implemented in HAWQ.
> > >
> > > Any ideas or suggestions are welcomed? Thanks.
> >
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Lili Ma <lm...@pivotal.io>.
What about we work out a draft design describing how to implement data
skipping technology for HAWQ?
Thanks
Lili
On Wed, Jul 6, 2016 at 7:23 PM, Gmail <xu...@gmail.com> wrote:
> BTW, could you create some related issues in JIRA?
>
> Thanks
> xunzhang
>
> Send from my iPhone
>
> > 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
> >
> > Data skipping technology can extremely avoiding unnecessary IO, so it
> can
> > extremely enhance performance for IO intensive query. Including
> eliminating
> > query on unnecessary table partition according to the partition key
> range ,
> > I think more options are available now:
> >
> > (1) Parquet / ORC format introduce a lightweight meta data info like
> > Min/Max/Bloom filter for each block, such meta data can be exploited when
> > predicate/filter info can be fetched before executing scan.
> >
> > However now in HAWQ, all data in parquet need to be scanned into memory
> > before processing predicate/filter. We don't generate the meta info when
> > INSERT into parquet table, the scan executor doesn't utilize the meta
> info
> > neither. Maybe some scan API need to be refactored so that we can get
> > predicate/filter
> > info before executing base relation scan.
> >
> > (2) Base on (1) technology, especially with Bloom filter, more optimizer
> > technology can be explored furthur. E.g. Impala implemented Runtime
> > filtering(*
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > <
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> >*
> > ), which can be used at
> > - dynamic partition pruning
> > - converting join predicate to base relation predicate
> >
> > It tell the executor to wait for one moment(the interval time can be set
> in
> > guc) before executing base relation scan, if the interested values(e.g.
> the
> > column in join predicate only have very small set) arrived in time, it
> can
> > use these value to filter this scan, if doesn't arrived in time, it scan
> > without this filter, which doesn't impact result correctness.
> >
> > Unlike (1) technology, this technology cannot be used in any case, it
> only
> > outperform in some cases. So it just add some more query plan
> > choices/paths, and the optimizer need based on statistics info to
> calculate
> > the cost, and apply it when cost down.
> >
> > All in one, maybe more similar technology can be adoptable for HAWQ now,
> > let's start to think about performance related technology, moreover we
> need
> > to instigate how these technology can be implemented in HAWQ.
> >
> > Any ideas or suggestions are welcomed? Thanks.
>
Re: [Propose] More data skipping technology for IO intensive performance enhancement
Posted by Gmail <xu...@gmail.com>.
BTW, could you create some related issues in JIRA?
Thanks
xunzhang
Send from my iPhone
> 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
>
> Data skipping technology can extremely avoiding unnecessary IO, so it can
> extremely enhance performance for IO intensive query. Including eliminating
> query on unnecessary table partition according to the partition key range ,
> I think more options are available now:
>
> (1) Parquet / ORC format introduce a lightweight meta data info like
> Min/Max/Bloom filter for each block, such meta data can be exploited when
> predicate/filter info can be fetched before executing scan.
>
> However now in HAWQ, all data in parquet need to be scanned into memory
> before processing predicate/filter. We don't generate the meta info when
> INSERT into parquet table, the scan executor doesn't utilize the meta info
> neither. Maybe some scan API need to be refactored so that we can get
> predicate/filter
> info before executing base relation scan.
>
> (2) Base on (1) technology, especially with Bloom filter, more optimizer
> technology can be explored furthur. E.g. Impala implemented Runtime
> filtering(*https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> <https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html>*
> ), which can be used at
> - dynamic partition pruning
> - converting join predicate to base relation predicate
>
> It tell the executor to wait for one moment(the interval time can be set in
> guc) before executing base relation scan, if the interested values(e.g. the
> column in join predicate only have very small set) arrived in time, it can
> use these value to filter this scan, if doesn't arrived in time, it scan
> without this filter, which doesn't impact result correctness.
>
> Unlike (1) technology, this technology cannot be used in any case, it only
> outperform in some cases. So it just add some more query plan
> choices/paths, and the optimizer need based on statistics info to calculate
> the cost, and apply it when cost down.
>
> All in one, maybe more similar technology can be adoptable for HAWQ now,
> let's start to think about performance related technology, moreover we need
> to instigate how these technology can be implemented in HAWQ.
>
> Any ideas or suggestions are welcomed? Thanks.
Re: [Propose] More data skipping technology for IO intensive performance enhancement
Posted by Gmail <xu...@gmail.com>.
Ming, thanks for the proposal. I really like it!!
Performance is the important reason why users choose HAWQ. I think we need to improve the implementation of parquet format 'insert' and 'scan' according to your idea. Meanwhile, for further formats such as ORC supporting, we need to use their index to speed up reading.
Looking forward to see the enhancement in new release of HAWQ soon.
Send from my iPhone
发自我的 iPhone
> 在 2016年7月2日，23:19，Ming Li <ml...@pivotal.io> 写道：
>
> Data skipping technology can extremely avoiding unnecessary IO, so it can
> extremely enhance performance for IO intensive query. Including eliminating
> query on unnecessary table partition according to the partition key range ,
> I think more options are available now:
>
> (1) Parquet / ORC format introduce a lightweight meta data info like
> Min/Max/Bloom filter for each block, such meta data can be exploited when
> predicate/filter info can be fetched before executing scan.
>
> However now in HAWQ, all data in parquet need to be scanned into memory
> before processing predicate/filter. We don't generate the meta info when
> INSERT into parquet table, the scan executor doesn't utilize the meta info
> neither. Maybe some scan API need to be refactored so that we can get
> predicate/filter
> info before executing base relation scan.
>
> (2) Base on (1) technology, especially with Bloom filter, more optimizer
> technology can be explored furthur. E.g. Impala implemented Runtime
> filtering(*https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> <https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html>*
> ), which can be used at
> - dynamic partition pruning
> - converting join predicate to base relation predicate
>
> It tell the executor to wait for one moment(the interval time can be set in
> guc) before executing base relation scan, if the interested values(e.g. the
> column in join predicate only have very small set) arrived in time, it can
> use these value to filter this scan, if doesn't arrived in time, it scan
> without this filter, which doesn't impact result correctness.
>
> Unlike (1) technology, this technology cannot be used in any case, it only
> outperform in some cases. So it just add some more query plan
> choices/paths, and the optimizer need based on statistics info to calculate
> the cost, and apply it when cost down.
>
> All in one, maybe more similar technology can be adoptable for HAWQ now,
> let's start to think about performance related technology, moreover we need
> to instigate how these technology can be implemented in HAWQ.
>
> Any ideas or suggestions are welcomed? Thanks.
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Lili Ma <lm...@pivotal.io>.
Agree! I think the data skipping technology is extremely important!
If we can push the filter down to the data file itself, we can save a lot
of scanning cost, and the efficiency will become more transparent when the
filtering can filter out a large part of data.
More and more open source formats such as parquet and ORC stores the
statistics information together with data, so this enables the push down of
the filtering.
Getting a fast response for a query among large tons of data becomes more
and more important, so I think we should investigate and further implement
this kind of technology in HAWQ.
Thanks
Lili
On Sat, Jul 2, 2016 at 11:19 PM, Ming Li <ml...@pivotal.io> wrote:
> Data skipping technology can extremely avoiding unnecessary IO, so it can
> extremely enhance performance for IO intensive query. Including eliminating
> query on unnecessary table partition according to the partition key range ,
> I think more options are available now:
>
> (1) Parquet / ORC format introduce a lightweight meta data info like
> Min/Max/Bloom filter for each block, such meta data can be exploited when
> predicate/filter info can be fetched before executing scan.
>
> However now in HAWQ, all data in parquet need to be scanned into memory
> before processing predicate/filter. We don't generate the meta info when
> INSERT into parquet table, the scan executor doesn't utilize the meta info
> neither. Maybe some scan API need to be refactored so that we can get
> predicate/filter
> info before executing base relation scan.
>
> (2) Base on (1) technology, especially with Bloom filter, more optimizer
> technology can be explored furthur. E.g. Impala implemented Runtime
> filtering(*
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> <
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> >*
> ), which can be used at
> - dynamic partition pruning
> - converting join predicate to base relation predicate
>
> It tell the executor to wait for one moment(the interval time can be set in
> guc) before executing base relation scan, if the interested values(e.g. the
> column in join predicate only have very small set) arrived in time, it can
> use these value to filter this scan, if doesn't arrived in time, it scan
> without this filter, which doesn't impact result correctness.
>
> Unlike (1) technology, this technology cannot be used in any case, it only
> outperform in some cases. So it just add some more query plan
> choices/paths, and the optimizer need based on statistics info to calculate
> the cost, and apply it when cost down.
>
> All in one, maybe more similar technology can be adoptable for HAWQ now,
> let's start to think about performance related technology, moreover we need
> to instigate how these technology can be implemented in HAWQ.
>
> Any ideas or suggestions are welcomed? Thanks.
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Jemish Patel <je...@gmail.com>.
+1 Great idea.
Jemish
On Tue, Jul 5, 2016 at 10:16 AM Shivram Mani <sh...@gmail.com> wrote:
> +1 on any form of predicate pushdown. Query planning/optimizer will have to
> be modified with the modified cost of plans with the reduced data.
>
> On Tue, Jul 5, 2016 at 9:37 AM, Kavinder Dhaliwal <kd...@pivotal.io>
> wrote:
>
> > This is an excellent idea that bring HAWQ up to speed with comparable
> > databases (Presto, Impala). In addition to taking advantage of the stats
> > available in file formats like ORC, HAWQ should also transition to
> > vectorized reading of files as this also provides a performance boost.
> The
> > newer Apache ORC library only supports vectorized reading so HAWQ should
> > also adopt these new methods.
> >
> > Kavinder
> >
> > On Mon, Jul 4, 2016 at 2:32 AM, Lei Chang <le...@apache.org> wrote:
> >
> > > Good idea. I think it can potentially increase the performance of IO
> > bound
> > > workload.
> > >
> > > Cheers
> > > Lei
> > >
> > >
> > > On Sat, Jul 2, 2016 at 11:19 PM, Ming Li <ml...@pivotal.io> wrote:
> > >
> > > > Data skipping technology can extremely avoiding unnecessary IO, so
> it
> > > can
> > > > extremely enhance performance for IO intensive query. Including
> > > eliminating
> > > > query on unnecessary table partition according to the partition key
> > > range ,
> > > > I think more options are available now:
> > > >
> > > > (1) Parquet / ORC format introduce a lightweight meta data info like
> > > > Min/Max/Bloom filter for each block, such meta data can be exploited
> > when
> > > > predicate/filter info can be fetched before executing scan.
> > > >
> > > > However now in HAWQ, all data in parquet need to be scanned into
> memory
> > > > before processing predicate/filter. We don't generate the meta info
> > when
> > > > INSERT into parquet table, the scan executor doesn't utilize the meta
> > > info
> > > > neither. Maybe some scan API need to be refactored so that we can get
> > > > predicate/filter
> > > > info before executing base relation scan.
> > > >
> > > > (2) Base on (1) technology, especially with Bloom filter, more
> > optimizer
> > > > technology can be explored furthur. E.g. Impala implemented Runtime
> > > > filtering(*
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > <
> > > >
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > > >*
> > > > ), which can be used at
> > > > - dynamic partition pruning
> > > > - converting join predicate to base relation predicate
> > > >
> > > > It tell the executor to wait for one moment(the interval time can be
> > set
> > > in
> > > > guc) before executing base relation scan, if the interested
> values(e.g.
> > > the
> > > > column in join predicate only have very small set) arrived in time,
> it
> > > can
> > > > use these value to filter this scan, if doesn't arrived in time, it
> > scan
> > > > without this filter, which doesn't impact result correctness.
> > > >
> > > > Unlike (1) technology, this technology cannot be used in any case, it
> > > only
> > > > outperform in some cases. So it just add some more query plan
> > > > choices/paths, and the optimizer need based on statistics info to
> > > calculate
> > > > the cost, and apply it when cost down.
> > > >
> > > > All in one, maybe more similar technology can be adoptable for HAWQ
> > now,
> > > > let's start to think about performance related technology, moreover
> we
> > > need
> > > > to instigate how these technology can be implemented in HAWQ.
> > > >
> > > > Any ideas or suggestions are welcomed? Thanks.
> > > >
> > >
> >
>
>
>
> --
> shivram mani
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Shivram Mani <sh...@gmail.com>.
+1 on any form of predicate pushdown. Query planning/optimizer will have to
be modified with the modified cost of plans with the reduced data.
On Tue, Jul 5, 2016 at 9:37 AM, Kavinder Dhaliwal <kd...@pivotal.io>
wrote:
> This is an excellent idea that bring HAWQ up to speed with comparable
> databases (Presto, Impala). In addition to taking advantage of the stats
> available in file formats like ORC, HAWQ should also transition to
> vectorized reading of files as this also provides a performance boost. The
> newer Apache ORC library only supports vectorized reading so HAWQ should
> also adopt these new methods.
>
> Kavinder
>
> On Mon, Jul 4, 2016 at 2:32 AM, Lei Chang <le...@apache.org> wrote:
>
> > Good idea. I think it can potentially increase the performance of IO
> bound
> > workload.
> >
> > Cheers
> > Lei
> >
> >
> > On Sat, Jul 2, 2016 at 11:19 PM, Ming Li <ml...@pivotal.io> wrote:
> >
> > > Data skipping technology can extremely avoiding unnecessary IO, so it
> > can
> > > extremely enhance performance for IO intensive query. Including
> > eliminating
> > > query on unnecessary table partition according to the partition key
> > range ,
> > > I think more options are available now:
> > >
> > > (1) Parquet / ORC format introduce a lightweight meta data info like
> > > Min/Max/Bloom filter for each block, such meta data can be exploited
> when
> > > predicate/filter info can be fetched before executing scan.
> > >
> > > However now in HAWQ, all data in parquet need to be scanned into memory
> > > before processing predicate/filter. We don't generate the meta info
> when
> > > INSERT into parquet table, the scan executor doesn't utilize the meta
> > info
> > > neither. Maybe some scan API need to be refactored so that we can get
> > > predicate/filter
> > > info before executing base relation scan.
> > >
> > > (2) Base on (1) technology, especially with Bloom filter, more
> optimizer
> > > technology can be explored furthur. E.g. Impala implemented Runtime
> > > filtering(*
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > <
> > >
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > > >*
> > > ), which can be used at
> > > - dynamic partition pruning
> > > - converting join predicate to base relation predicate
> > >
> > > It tell the executor to wait for one moment(the interval time can be
> set
> > in
> > > guc) before executing base relation scan, if the interested values(e.g.
> > the
> > > column in join predicate only have very small set) arrived in time, it
> > can
> > > use these value to filter this scan, if doesn't arrived in time, it
> scan
> > > without this filter, which doesn't impact result correctness.
> > >
> > > Unlike (1) technology, this technology cannot be used in any case, it
> > only
> > > outperform in some cases. So it just add some more query plan
> > > choices/paths, and the optimizer need based on statistics info to
> > calculate
> > > the cost, and apply it when cost down.
> > >
> > > All in one, maybe more similar technology can be adoptable for HAWQ
> now,
> > > let's start to think about performance related technology, moreover we
> > need
> > > to instigate how these technology can be implemented in HAWQ.
> > >
> > > Any ideas or suggestions are welcomed? Thanks.
> > >
> >
>
--
shivram mani
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Kavinder Dhaliwal <kd...@pivotal.io>.
This is an excellent idea that bring HAWQ up to speed with comparable
databases (Presto, Impala). In addition to taking advantage of the stats
available in file formats like ORC, HAWQ should also transition to
vectorized reading of files as this also provides a performance boost. The
newer Apache ORC library only supports vectorized reading so HAWQ should
also adopt these new methods.
Kavinder
On Mon, Jul 4, 2016 at 2:32 AM, Lei Chang <le...@apache.org> wrote:
> Good idea. I think it can potentially increase the performance of IO bound
> workload.
>
> Cheers
> Lei
>
>
> On Sat, Jul 2, 2016 at 11:19 PM, Ming Li <ml...@pivotal.io> wrote:
>
> > Data skipping technology can extremely avoiding unnecessary IO, so it
> can
> > extremely enhance performance for IO intensive query. Including
> eliminating
> > query on unnecessary table partition according to the partition key
> range ,
> > I think more options are available now:
> >
> > (1) Parquet / ORC format introduce a lightweight meta data info like
> > Min/Max/Bloom filter for each block, such meta data can be exploited when
> > predicate/filter info can be fetched before executing scan.
> >
> > However now in HAWQ, all data in parquet need to be scanned into memory
> > before processing predicate/filter. We don't generate the meta info when
> > INSERT into parquet table, the scan executor doesn't utilize the meta
> info
> > neither. Maybe some scan API need to be refactored so that we can get
> > predicate/filter
> > info before executing base relation scan.
> >
> > (2) Base on (1) technology, especially with Bloom filter, more optimizer
> > technology can be explored furthur. E.g. Impala implemented Runtime
> > filtering(*
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > <
> >
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> > >*
> > ), which can be used at
> > - dynamic partition pruning
> > - converting join predicate to base relation predicate
> >
> > It tell the executor to wait for one moment(the interval time can be set
> in
> > guc) before executing base relation scan, if the interested values(e.g.
> the
> > column in join predicate only have very small set) arrived in time, it
> can
> > use these value to filter this scan, if doesn't arrived in time, it scan
> > without this filter, which doesn't impact result correctness.
> >
> > Unlike (1) technology, this technology cannot be used in any case, it
> only
> > outperform in some cases. So it just add some more query plan
> > choices/paths, and the optimizer need based on statistics info to
> calculate
> > the cost, and apply it when cost down.
> >
> > All in one, maybe more similar technology can be adoptable for HAWQ now,
> > let's start to think about performance related technology, moreover we
> need
> > to instigate how these technology can be implemented in HAWQ.
> >
> > Any ideas or suggestions are welcomed? Thanks.
> >
>
Re: [Propose] More data skipping technology for IO intensive
performance enhancement
Posted by Lei Chang <le...@apache.org>.
Good idea. I think it can potentially increase the performance of IO bound
workload.
Cheers
Lei
On Sat, Jul 2, 2016 at 11:19 PM, Ming Li <ml...@pivotal.io> wrote:
> Data skipping technology can extremely avoiding unnecessary IO, so it can
> extremely enhance performance for IO intensive query. Including eliminating
> query on unnecessary table partition according to the partition key range ,
> I think more options are available now:
>
> (1) Parquet / ORC format introduce a lightweight meta data info like
> Min/Max/Bloom filter for each block, such meta data can be exploited when
> predicate/filter info can be fetched before executing scan.
>
> However now in HAWQ, all data in parquet need to be scanned into memory
> before processing predicate/filter. We don't generate the meta info when
> INSERT into parquet table, the scan executor doesn't utilize the meta info
> neither. Maybe some scan API need to be refactored so that we can get
> predicate/filter
> info before executing base relation scan.
>
> (2) Base on (1) technology, especially with Bloom filter, more optimizer
> technology can be explored furthur. E.g. Impala implemented Runtime
> filtering(*
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> <
> https://www.cloudera.com/documentation/enterprise/latest/topics/impala_runtime_filtering.html
> >*
> ), which can be used at
> - dynamic partition pruning
> - converting join predicate to base relation predicate
>
> It tell the executor to wait for one moment(the interval time can be set in
> guc) before executing base relation scan, if the interested values(e.g. the
> column in join predicate only have very small set) arrived in time, it can
> use these value to filter this scan, if doesn't arrived in time, it scan
> without this filter, which doesn't impact result correctness.
>
> Unlike (1) technology, this technology cannot be used in any case, it only
> outperform in some cases. So it just add some more query plan
> choices/paths, and the optimizer need based on statistics info to calculate
> the cost, and apply it when cost down.
>
> All in one, maybe more similar technology can be adoptable for HAWQ now,
> let's start to think about performance related technology, moreover we need
> to instigate how these technology can be implemented in HAWQ.
>
> Any ideas or suggestions are welcomed? Thanks.
>