Skip to content

Instantly share code, notes, and snippets.

@ruz
Created November 11, 2011 00:29
Show Gist options
  • Save ruz/1356744 to your computer and use it in GitHub Desktop.
Save ruz/1356744 to your computer and use it in GitHub Desktop.
Postgres data correlation problem
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('dbi:Pg:dbname=test', 'postgres', undef, { RaiseError => 1 });
$dbh->do(q{DROP TABLE IF EXISTS test});
$dbh->do(q{
CREATE TABLE test (
id INT NOT NULL PRIMARY KEY,
ColX INT NOT NULL
)
});
my $total = 1000;
my $sth = $dbh->prepare('INSERT INTO test VALUES (?, ?)');
for ( my $i = 1; $i < $total; $i++ ) {
my $x = $i;
if ( rand(1) >= 0.95 ) {
$x = int(rand($total)) + 1;
}
$sth->execute( $i, $x);
}
use Data::Dumper;
$Data::Dumper::Indent = 1;
$dbh->do(q{CREATE INDEX T1 ON test(ColX);});
$dbh->do(q{CREATE INDEX T2 ON test(id, ColX);});
#$dbh->do(q{ALTER TABLE test ALTER COLUMN ColX SET STATISTICS 0});
$dbh->do(q{ANALYZE VERBOSE test});
print Dumper $dbh->selectall_arrayref(
q{SELECT * FROM pg_stats WHERE tablename = 'test'},
{Slice => {} }
);
print Dumper $dbh->selectall_arrayref(
q{EXPLAIN ANALYZE SELECT * FROM test WHERE id = ColX},
{Slice => {} }
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment