From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Sun, 20 May 2012 19:41:52 +0000 (+0200)
Subject: create indexes on inherited tables
X-Git-Url: http://git.rot13.org/?p=APKPM.git;a=commitdiff_plain;h=20f7f350129650225b5465bc95752ad955e6ecb3

create indexes on inherited tables
---

diff --git a/lib/APKPM/Store.pm b/lib/APKPM/Store.pm
index e04fda9..9e5ac67 100644
--- a/lib/APKPM/Store.pm
+++ b/lib/APKPM/Store.pm
@@ -27,6 +27,42 @@ sub dbh {
 our $redis;
 sub redis { $redis ||= Redis->new };
 
+sub _create_index {
+	my ( $self, $table, $timestamp ) = @_;
+
+	my $sth = $self->dbh->prepare( qq{
+
+select
+    t.relname as table_name,
+    i.relname as index_name,
+    a.attname as column_name
+from
+    pg_class t,
+    pg_class i,
+    pg_index ix,
+    pg_attribute a
+where
+    t.oid = ix.indrelid
+    and i.oid = ix.indexrelid
+    and a.attrelid = t.oid
+    and a.attnum = ANY(ix.indkey)
+    and t.relkind = 'r'
+    and t.relname like ?
+order by
+    t.relname,
+    i.relname;
+
+	} );
+	$sth->execute($table);
+	my $sql = '';
+	while( my $row = $sth->fetchrow_hashref ) {
+		#warn "# _create_index $table ",dump($row);
+		$sql .= qq|create index $row->{index_name}_$timestamp on ${table}_$timestamp($row->{column_name});\n|;
+	}
+
+	return $sql;
+}
+
 sub pg_insert {
 	my ( $self, $table, $h ) = @_;
 
@@ -44,6 +80,7 @@ sub pg_insert {
 		$create_table = qq{
 			create table $part () inherits ($table) ;
 		};
+		$create_table .= $self->_create_index( $table, $timestamp );
 		$table = $part;
 		warn "# using partition $table";
 	}
diff --git a/t/Store.t b/t/Store.t
index 0579f49..02848ee 100755
--- a/t/Store.t
+++ b/t/Store.t
@@ -2,7 +2,7 @@
 use strict;
 use warnings;
 
-use Test::More tests => 16;
+use Test::More tests => 17;
 use Data::Dump qw(dump);
 
 use lib 'lib';
@@ -11,6 +11,9 @@ use_ok 'APKPM::Store';
 
 ok my $o = APKPM::Store->new, 'new';
 
+ok my $sql = $o->_create_index('dslam', '2012_04_20'), '_create_index';
+diag $sql;
+
 ok my $r = $o->insert('job',{
 _table => 'ping',
 ip => '127.0.0.1',