danolivo / pgdev Goto Github PK

This project forked from postgres/postgres

My experiments with mirror of the official PostgreSQL GIT repository.

License: Other

Emacs Lisp 0.01% Makefile 0.66% M4 0.35% Shell 0.14% C 85.00% PLpgSQL 6.63% Perl 4.06% C++ 0.06% Yacc 1.25% Lex 0.41% Ruby 0.53% Python 0.10% Assembly 0.01% Roff 0.03% sed 0.01% DTrace 0.01% XS 0.01% PLSQL 0.10% Meson 0.65% Starlark 0.01%

pgdev's People

Contributors

Stargazers

Watchers

pgdev's Issues

Could we replace x==x with NullTest node without any additional actions?

I mean here, we should delete the restriction before changing and propagate by distribute_restrictinfo_to_rels() as designed in remove_rel_from_query

Explicit tests on visibility anomalies

Logical additions to physical time

We use physical time to create timestamp. But we correct it with some type of logical time (not wait for timeout) in the case of any problems. Analyze and test this aspect of our implementation.

Tests on time divergence problem

We need 3 nodes with unstable clock. We must switch the time during the test randomly.

Do we really need Writing CSN to the WAL?

We do it to solve a narrow problem. Analyze: why we haven't another way.

That about the same CSN values for two transactions from different nodes that update the same tuple.

ERROR: CTE in subquery, references upper relation

ERROR: plan should not reference subplan's variable

Replay:
EXPLAIN (COSTS OFF)
SELECT * FROM subselect_tbl a
WHERE a.f1 IN (
WITH cte AS (
SELECT * FROM subselect_tbl c WHERE f1 < 42 AND f2 = a.f1
)
SELECT b.f2 FROM cte b WHERE b.f2 = a.f2 AND b.f2 < 12
)
;

Check possible complex expressions in a target list of an unnesting subquery

	querytree->hasAggs ||
	querytree->hasWindowFuncs ||
	querytree->hasTargetSRFs ||
	querytree->hasSubLinks ||
	querytree->cteList ||
	querytree->rtable ||
	querytree->jointree->fromlist ||
	querytree->jointree->quals ||
	querytree->groupClause ||
	querytree->groupingSets ||
	querytree->havingQual ||
	querytree->windowClause ||
	querytree->distinctClause ||
	querytree->sortClause ||
	querytree->limitOffset ||
	querytree->limitCount ||
	querytree->setOperations ||

We need something similar to formal proof for our implementation of Clock-SI

Snapshot too old

In many tests on AWS we cought the error "Snapshot too old". In all tests difference in time was 5 seconds. It may be a bug. But it may be an internal problem of AWS clocks.

Theoretical options for SJR

Techniqiues:
2. INNER JOIN Elimination
3. OUTER JOIN Elimination
4. OUTER JOIN Elimination with DISTINCT

Which of these techniques implemented (or can be possible implemented with our approach)?

Advantages:

Performance
More precise cardinality estimation
Less number of entities at the stage of optimizing

Disadvantages

Overhead variants:

we have many self joins but no one can be removed
we have many joins, but no one SJ exist
pricey procedure of SJ elimination

See:

Implementation of Two Semantic Query Optimization Techniques in DB2 Universal Database
https://www.researchgate.net/publication/221309776_Implementation_of_Two_Semantic_Query_Optimization_Techniques_in_DB2_Universal_Database
https://blog.jooq.org/join-elimination-an-essential-optimiser-feature-for-advanced-sql-usage/

Small questions on corner cases of SJ detection

explain (COSTS OFF)
SELECT * FROM pg_am am WHERE am.amname IN (
  SELECT c1.relname AS relname
  FROM pg_class c1
    JOIN pg_class c2
    ON c1.oid=c2.oid AND c1.oid < 10
	JOIN pg_class c3 ON (c3.oid = c2.oid)
);

Result is OK:

 Hash Semi Join
   Hash Cond: (am.amname = c3.relname)
   ->  Seq Scan on pg_am am
   ->  Hash
         ->  Index Scan using pg_class_oid_index on pg_class c3
               Index Cond: ((oid < '10'::oid) AND (oid IS NOT NULL))

Query N0.2:

explain (COSTS OFF)
SELECT * FROM pg_am am WHERE am.amname IN (
  SELECT c1.relname AS relname
  FROM pg_class c1
    JOIN pg_class c2
    ON c1.oid=c2.oid AND c1.oid < 10
	JOIN pg_class c3 ON (c1.oid = 1 AND c3.oid = 1)
);

No one JOIN was deleted:

 Nested Loop Semi Join
   Join Filter: (am.amname = c1.relname)
   ->  Seq Scan on pg_am am
   ->  Materialize
         ->  Nested Loop
               ->  Nested Loop
                     ->  Index Scan using pg_class_oid_index on pg_class c1
                           Index Cond: ((oid < '10'::oid) AND (oid = '1'::oid))
                     ->  Index Only Scan using pg_class_oid_index on pg_class c2
                           Index Cond: (oid = '1'::oid)
               ->  Index Only Scan using pg_class_oid_index on pg_class c3
                     Index Cond: (oid = '1'::oid)

Query N0.3:

SELECT * FROM pg_am am WHERE am.amname IN (
  SELECT c1.relname AS relname
  FROM pg_class c1
    JOIN pg_class c2
    ON c1.oid=c2.oid AND c1.oid < 10
      JOIN pg_class c3 ON (c3.oid = 1 AND c1.oid = 1)
);

Only one JOIN was deleted:

  Nested Loop Semi Join
    Join Filter: (am.amname = c2.relname)
    ->  Seq Scan on pg_am am
    ->  Materialize
          ->  Nested Loop
                ->  Index Scan using pg_class_oid_index on pg_class c2
                      Index Cond: ((oid < '10'::oid) AND (oid IS NOT NULL))
                ->  Index Only Scan using pg_class_oid_index on pg_class c3
                      Index Cond: (oid = '1'::oid)

violated rule: "xact confirmed as committed, so any following xact must see its effects"

Currently, the rule "xact confirmed as committed, so any following xact must see its effects" might be violated.
Example:
Time(node1) >> Time(node2)
user executes transaction No.1 at node1 with time=10, update data at node1 and node2 and commit.
The same user start transaction No.2 at node2 with time=5 and can't see changes of committed (user done this sometimes ago) transaction.
Maybe it is not a problem of distributed DBMS? Maybe we should consider this as a feature of distributed transactions?

Allow expressions in target list of correlated subquery

That I mean we could allow (an example):
EXPLAIN (COSTS OFF)
SELECT * FROM subselect_tbl a
WHERE a.f1 IN (SELECT b.f2*b.f1/b.f3+2 FROM subselect_tbl b WHERE b.f3 = a.f2)
;

danolivo / pgdev Goto Github PK

pgdev's People

Contributors

Stargazers

Watchers

pgdev's Issues

Recommend Projects

Recommend Topics

Recommend Org