Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit54dd048

Browse files
committed
fixed sorting, merge append and index scan issues
1 parent66cd411 commit54dd048

File tree

5 files changed

+681
-60
lines changed

5 files changed

+681
-60
lines changed

‎expected/pg_pathman.out

Lines changed: 152 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -340,6 +340,26 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1
340340
-> Seq Scan on num_range_rel_4
341341
(8 rows)
342342

343+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel ORDER BY id;
344+
QUERY PLAN
345+
----------------------------------------------------------------
346+
Append
347+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
348+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2
349+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
350+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4
351+
(5 rows)
352+
353+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id <= 2500 ORDER BY id;
354+
QUERY PLAN
355+
----------------------------------------------------------------
356+
Append
357+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
358+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2
359+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
360+
Index Cond: (id <= 2500)
361+
(5 rows)
362+
343363
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
344364
QUERY PLAN
345365
------------------------------------------------------------------------------------
@@ -380,6 +400,138 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND d
380400
-> Seq Scan on range_rel_4
381401
(8 rows)
382402

403+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel ORDER BY dt;
404+
QUERY PLAN
405+
----------------------------------------------------------
406+
Append
407+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
408+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
409+
-> Index Scan using range_rel_3_dt_idx on range_rel_3
410+
-> Index Scan using range_rel_4_dt_idx on range_rel_4
411+
(5 rows)
412+
413+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-01-15' ORDER BY dt DESC;
414+
QUERY PLAN
415+
-------------------------------------------------------------------------------------
416+
Append
417+
-> Index Scan Backward using range_rel_4_dt_idx on range_rel_4
418+
-> Index Scan Backward using range_rel_3_dt_idx on range_rel_3
419+
-> Index Scan Backward using range_rel_2_dt_idx on range_rel_2
420+
-> Index Scan Backward using range_rel_1_dt_idx on range_rel_1
421+
Index Cond: (dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
422+
(6 rows)
423+
424+
/*
425+
* Sorting
426+
*/
427+
SET enable_indexscan = OFF;
428+
SET enable_seqscan = ON;
429+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt < '2015-03-01' ORDER BY dt;
430+
QUERY PLAN
431+
-------------------------------------
432+
Sort
433+
Sort Key: range_rel_1.dt
434+
-> Append
435+
-> Seq Scan on range_rel_1
436+
-> Seq Scan on range_rel_2
437+
(5 rows)
438+
439+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.range_rel_2 ORDER BY dt;
440+
QUERY PLAN
441+
-------------------------------------
442+
Sort
443+
Sort Key: range_rel_1.dt
444+
-> Append
445+
-> Seq Scan on range_rel_1
446+
-> Seq Scan on range_rel_2
447+
(5 rows)
448+
449+
SET enable_indexscan = ON;
450+
SET enable_seqscan = OFF;
451+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt < '2015-03-01' ORDER BY dt;
452+
QUERY PLAN
453+
----------------------------------------------------------
454+
Append
455+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
456+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
457+
(3 rows)
458+
459+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.range_rel_2 ORDER BY dt;
460+
QUERY PLAN
461+
----------------------------------------------------------
462+
Merge Append
463+
Sort Key: range_rel_1.dt
464+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
465+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
466+
(4 rows)
467+
468+
/*
469+
* Join
470+
*/
471+
SET enable_hashjoin = OFF;
472+
SET enable_mergejoin = ON;
473+
EXPLAIN (COSTS OFF)
474+
SELECT * FROM test.range_rel j1
475+
JOIN test.range_rel j2 on j2.id = j1.id
476+
JOIN test.num_range_rel j3 on j3.id = j1.id
477+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
478+
QUERY PLAN
479+
-------------------------------------------------------------------------------------------
480+
Sort
481+
Sort Key: j2.dt
482+
-> Merge Join
483+
Merge Cond: (j3.id = j2.id)
484+
-> Append
485+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
486+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
487+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
488+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
489+
-> Materialize
490+
-> Merge Join
491+
Merge Cond: (j1.id = j2.id)
492+
-> Merge Append
493+
Sort Key: j1.id
494+
-> Index Scan using range_rel_1_pkey on range_rel_1 j1
495+
-> Index Scan using range_rel_2_pkey on range_rel_2 j1_1
496+
-> Materialize
497+
-> Merge Append
498+
Sort Key: j2.id
499+
-> Index Scan using range_rel_2_pkey on range_rel_2 j2
500+
-> Index Scan using range_rel_3_pkey on range_rel_3 j2_1
501+
-> Index Scan using range_rel_4_pkey on range_rel_4 j2_2
502+
(22 rows)
503+
504+
SET enable_hashjoin = ON;
505+
SET enable_mergejoin = OFF;
506+
EXPLAIN (COSTS OFF)
507+
SELECT * FROM test.range_rel j1
508+
JOIN test.range_rel j2 on j2.id = j1.id
509+
JOIN test.num_range_rel j3 on j3.id = j1.id
510+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
511+
QUERY PLAN
512+
---------------------------------------------------------------------------------------
513+
Sort
514+
Sort Key: j2.dt
515+
-> Hash Join
516+
Hash Cond: (j1.id = j2.id)
517+
-> Hash Join
518+
Hash Cond: (j3.id = j1.id)
519+
-> Append
520+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
521+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
522+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
523+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
524+
-> Hash
525+
-> Append
526+
-> Index Scan using range_rel_1_pkey on range_rel_1 j1
527+
-> Index Scan using range_rel_2_pkey on range_rel_2 j1_1
528+
-> Hash
529+
-> Append
530+
-> Index Scan using range_rel_2_dt_idx on range_rel_2 j2
531+
-> Index Scan using range_rel_3_dt_idx on range_rel_3 j2_1
532+
-> Index Scan using range_rel_4_dt_idx on range_rel_4 j2_2
533+
(20 rows)
534+
383535
/*
384536
* Test CTE query
385537
*/

‎init.c

Lines changed: 23 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@
2121
#include"utils/lsyscache.h"
2222
#include"utils/bytea.h"
2323
#include"utils/snapmgr.h"
24+
#include"optimizer/clauses.h"
2425

2526

2627
HTAB*relations=NULL;
@@ -32,6 +33,7 @@ static bool globalByVal;
3233

3334
staticboolvalidate_range_constraint(Expr*,PartRelationInfo*,Datum*,Datum*);
3435
staticboolvalidate_hash_constraint(Expr*expr,PartRelationInfo*prel,int*hash);
36+
staticboolread_opexpr_const(OpExpr*opexpr,intvarattno,Datum*val);
3537
staticintcmp_range_entries(constvoid*p1,constvoid*p2);
3638

3739
Size
@@ -453,11 +455,7 @@ validate_range_constraint(Expr *expr, PartRelationInfo *prel, Datum *min, Datum
453455
OpExpr*opexpr;
454456

455457
/* it should be an AND operator on top */
456-
if ( !(IsA(expr,BoolExpr)&&boolexpr->boolop==AND_EXPR) )
457-
return false;
458-
459-
/* and it should have exactly two operands */
460-
if (list_length(boolexpr->args)!=2)
458+
if (!and_clause((Node*)expr))
461459
return false;
462460

463461
tce=lookup_type_cache(prel->atttype,TYPECACHE_EQ_OPR |TYPECACHE_LT_OPR |TYPECACHE_GT_OPR);
@@ -466,36 +464,43 @@ validate_range_constraint(Expr *expr, PartRelationInfo *prel, Datum *min, Datum
466464
opexpr= (OpExpr*)linitial(boolexpr->args);
467465
if (get_op_opfamily_strategy(opexpr->opno,tce->btree_opf)==BTGreaterEqualStrategyNumber)
468466
{
469-
Node*left=linitial(opexpr->args);
470-
Node*right=lsecond(opexpr->args);
471-
if ( !IsA(left,Var)|| !IsA(right,Const) )
472-
return false;
473-
if ( ((Var*)left)->varattno!=prel->attnum )
467+
if (!read_opexpr_const(opexpr,prel->attnum,min))
474468
return false;
475-
*min= ((Const*)right)->constvalue;
476469
}
477470
else
478471
return false;
479472

480-
/* TODO: rewrite this */
481473
/* check that right operand is < operator */
482474
opexpr= (OpExpr*)lsecond(boolexpr->args);
483475
if (get_op_opfamily_strategy(opexpr->opno,tce->btree_opf)==BTLessStrategyNumber)
484476
{
485-
Node*left=linitial(opexpr->args);
486-
Node*right=lsecond(opexpr->args);
487-
if ( !IsA(left,Var)|| !IsA(right,Const) )
488-
return false;
489-
if ( ((Var*)left)->varattno!=prel->attnum )
477+
if (!read_opexpr_const(opexpr,prel->attnum,max))
490478
return false;
491-
*max= ((Const*)right)->constvalue;
492479
}
493480
else
494481
return false;
495482

496483
return true;
497484
}
498485

486+
/*
487+
* Reads const value from expressions of kind: VAR >= CONST or VAR < CONST
488+
*/
489+
staticbool
490+
read_opexpr_const(OpExpr*opexpr,intvarattno,Datum*val)
491+
{
492+
Node*left=linitial(opexpr->args);
493+
Node*right=lsecond(opexpr->args);
494+
495+
if ( !IsA(left,Var)|| !IsA(right,Const) )
496+
return false;
497+
if ( ((Var*)left)->varattno!=varattno )
498+
return false;
499+
*val= ((Const*)right)->constvalue;
500+
501+
return true;
502+
}
503+
499504
/*
500505
* Validate hash constraint. It MUST have the exact format
501506
* VARIABLE % CONST = CONST

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp