1+ use strict;
2+ use warnings;
3+
4+ use Config;
5+ use PostgreSQL::Test::Cluster;
6+ use PostgreSQL::Test::Utils;
7+
8+ use Test::Moretests => 2;
9+
10+ my $node = PostgreSQL::Test::Cluster-> new(' aqotest'
11+ $node -> init;
12+ $node -> append_conf(' postgresql.conf' qq{
13+ shared_preload_libraries = 'aqo'
14+ log_statement = 'ddl'
15+ aqo.join_threshold = 0
16+ aqo.mode = 'learn'
17+ aqo.show_details = 'on'
18+ aqo.show_hash = 'on'
19+ aqo.min_neighbors_for_predicting = 1
20+ enable_nestloop = 'off'
21+ enable_mergejoin = 'off'
22+ enable_material = 'off'
23+ }
24+
25+ $node -> start();
26+ $node -> safe_psql(' postgres' ' CREATE EXTENSION aqo'
27+
28+ # Create tables with correlated datas in columns
29+
30+ $node -> safe_psql(' postgres' ' CREATE TABLE a (x1 int, x2 int, x3 int);
31+ INSERT INTO a (x1, x2, x3) SELECT mod(ival,10), mod(ival,10), mod(ival,10) FROM generate_series(1,1000) As ival' );
32+
33+ $node -> safe_psql(' postgres' ' CREATE TABLE b (y1 int, y2 int, y3 int);
34+ INSERT INTO b (y1, y2, y3) SELECT mod(ival + 1,10), mod(ival + 1,10), mod(ival + 1,10) FROM generate_series(1,1000) As ival' );
35+
36+ my $result ;
37+
38+ my $plan =$node -> safe_psql(' postgres' ' EXPLAIN (analyze true, verbose true)
39+ SELECT a.x1, b.y1, COUNT(*) FROM a, b WHERE a.x2 = b.y2 GROUP BY a.x1, b.y1;' );
40+ my @fss =$plan =~/ fss=(-?\d +)/g
41+
42+ $result =$node -> safe_psql(' postgres' ' SELECT count(*) FROM aqo_data;'
43+ is($result , 4);
44+
45+ $result =$node -> safe_psql(' postgres' ' SELECT fss FROM aqo_data;'
46+
47+ my @storage =split (/ \n / $result );
48+
49+ # compare fss from plan and fss from storage
50+ my $test2 = 1;
51+ if (scalar @fss ==scalar @storage ) {
52+ foreach my $numb1 (@fss ) {
53+ my $found = 0;
54+
55+ # check fss not zero
56+ if ($numb1 == 0) {
57+ $test2 = 0;
58+ last ;
59+ }
60+
61+ foreach my $numb2 (@storage ) {
62+ if ($numb2 ==$numb1 ) {
63+ $found = 1;
64+ last ;
65+ }
66+ }
67+
68+ if (!$found ) {
69+ $test2 = 0;
70+ last ;
71+ }
72+ }
73+ }else {
74+ $test2 = 0;
75+ }
76+
77+ is($test2 , 1);
78+
79+ $node -> stop();
