Database join operations with early filtering
Aspects of the disclosure are directed to early filtering of candidate rows for a join operator of a query statement before the join operator is evaluated to generate a result set. Early filtering, e.g., before execution of the join operator, reduces the number of candidate rows fetched from a database during a join operator, which can improve the rate at which queries including join operators are executed by a DBMS for the database. One or more Bloom filters or other probabilistic data structures can be produced and consumed according to different join conditions, allowing for early pruning of unqualified rows during a database scan operation.
1. A system comprising one or more processors, the one or more processors configured to:
determine whether to perform early filtering of attributes input to a join operator before executing the join operator including:
determining a ratio between qualified records to a total row count of data sources targeted by the join operator; and
perform early filtering including:
generating a list of outer and inner attributes targeted by the join operator;
generating a producer filter map and a consumer filter map by using the list of outer and inner attributes;
generating a filter for each attribute in the list of outer and inner attributes;
storing one or more generated filters in the consumer filter map;
storing a pointer of each of the one or more generated filters in the producer filter map and the consumer filter map;
using the producer filter map to fill data into the one or more generated filters, wherein the producer filter map includes data associated with the list of outer and inner attributes;
using the consumer filter map to search for one or more generated filters for one or more join conditions associated with the join operator, wherein the consumer filter map associates each of the one or more generated filters with the one or more join conditions; and
applying the one or more generated filters as part of executing a scan operator, before executing the join operator.
2. The system of claim 1 , wherein the one or more generated filters are Bloom filters.
3. The system of claim 2 , wherein the one or more processors are further configured to:
generate a list of inner attributes from the list of outer and inner attributes; and
store the list of inner attributes in memory.
4. The system of claim 2 , wherein the one or more processors are further configured to:
determine, for an outer attribute, that a corresponding Bloom filter has been generated; and
filter the outer attribute according to the corresponding Bloom filter.
5. The system of claim 2 , wherein in determining whether to perform early filtering, the one or more processors are further configured to:
determine whether the ratio satisfies a predetermined ratio.
6. The system of claim 2 , wherein in determining whether to perform early filtering, the one or more processors are further configured to:
determine whether the join operator specifies the one or more join conditions in which a first child operator of the join operator has one or more tables associated with the first child operator materialized before one or more tables of a second child operator of the join operator.
7. The system of claim 2 , wherein in determining whether to perform early filtering, the one or more processors are further configured to:
determine whether the join operator is a hash join operator.
8. A method comprising:
determining, by one or more processors, whether to perform early filtering of attributes input to a join operator before executing the join operator including:
determining a ratio between qualified records to a total row count of data sources targeted by the join operator; and
performing early filtering by:
generating, by the one or more processors, a list of outer and inner attributes targeted by the join operator,
generating or updating, by the one or more processors, a producer filter map and a consumer filter map, using the list of outer and inner attributes,
generating a filter for each attribute in the list of outer and inner attributes,
storing one or more generated filters in the consumer filter map,
storing a pointer of each of the one or more generated filters in the producer filter map and the consumer filter map,
using the producer filter map to fill data into the one or more generated filters, wherein the producer filter map includes data associated with the list of outer and inner attributes,
using the consumer filter map to search for one or more generated filters for one or more join conditions associated with the join operator, wherein the consumer filter map associates each of the one or more generated filters with the one or more join conditions, and
applying, by the one or more processors, the one or more generated filters as part of executing a scan operator, before executing the join operator.
9. The method of claim 8 , wherein the one or more generated filters are Bloom filters.
10. The method of claim 9 , wherein the method further comprises:
generating a list of inner attributes from the list of outer and inner attributes; and
storing the list of inner attributes in memory.
11. The method of claim 9 , wherein the method further comprises:
determining, for an outer attribute, that a corresponding Bloom filter has been generated, and
filtering the outer attribute according to the corresponding Bloom filter.
12. The method of claim 9 , wherein determining whether to perform early filtering comprises:
determining whether the ratio satisfies a predetermined ratio.
13. The method of claim 9 , wherein determining whether to perform early filtering comprises:
determining whether the join operator specifies the one or more join conditions in which a first child operator of the join operator has one or more tables associated with the first child operator materialized before one or more tables of a second child operator of the join operator.
14. The method of claim 9 , wherein determining whether to perform early filtering comprises:
determining whether the join operator is a hash join operator.
15. One or more non-transitory computer-readable storage media storing instructions that, when executed by one or more processors, cause the one or more processors to perform operations comprising:
determining whether to perform early filtering of attributes input to a join operator before executing the join operator including determining a ratio between qualified records to a total row count of data sources targeted by the join operator; and
performing early filtering including:
generating a list of outer and inner attributes targeted by the join operator,
generating or updating a producer filter map and a consumer filter map, using the list of outer and inner attributes,
generating a filter for each attribute in the list of outer and inner attributes,
storing one or more generated filters in the consumer filter map,
storing a pointer of each of the one or more generated filters in the producer filter map and the consumer filter map,
using the producer filter map to fill data into the one or more generated filters, wherein the producer filter map includes data associated with the list of outer and inner attributes,
using the consumer filter map to search for one or more generated filters for one or more join conditions associated with the join operator, wherein the consumer filter map associates each of the one or more generated filters with the one or more join conditions, and
applying the one or more generated filters as part of executing a scan operator, before executing the join operator.
16. The one or more non-transitory computer-readable storage media of claim 15 , wherein the one or more generated filters are Bloom filters.
17. The one or more non-transitory computer-readable storage media of claim 16 , wherein the instructions cause the one or more processors to perform operations comprising:
generating a list of inner attributes from the list of outer and inner attributes; and
storing the list of inner attributes in memory.
18. The one or more non-transitory computer-readable storage media of claim 16 , wherein the instructions cause the one or more processors to perform operations comprising:
determining, for an outer attribute, that a corresponding Bloom filter has been generated, and
filtering the outer attribute according to the corresponding Bloom filter.
19. The one or more non-transitory computer-readable storage media of claim 16 , wherein determining whether to perform early filtering comprises:
determining whether the ratio satisfies a predetermined ratio.
20. The one or more non-transitory computer-readable storage media of claim 16 , wherein determining whether to perform early filtering comprises:
determining whether the join operator specifies the one or more join conditions in which a first child operator of the join operator has one or more tables associated with the first child operator materialized before one or more tables of a second child operator of the join operator.