Re: WIP: Transportable Optimizer Mode - Mailing list pgsql-patches

simon@2ndquadrant.com (Simon Riggs) writes:
> Comments welcome.

A minor change: I'd suggest creating these views as TEMP views, that
way they go away automatically.

diff -r1.1 create_tom_tables.sql
===================================================================
RCS file: get_data_for_tom.sql,v
retrieving revision 1.1
diff -r1.1 get_data_for_tom.sql
8,9c8
< DROP VIEW IF EXISTS tom_get_stats;
< CREATE OR REPLACE VIEW tom_get_stats AS
---
> CREATE OR REPLACE TEMP VIEW tom_get_stats AS
54,55c53
< DROP VIEW IF EXISTS tom_get_pg_class;
< CREATE OR REPLACE VIEW tom_get_pg_class AS
---
> CREATE OR REPLACE TEMP VIEW tom_get_pg_class AS
71,73c69,71
< ORDER BY n.nspname
< ;
< \copy (select * from get_tom_pg_class) TO 'tom_pg_class.data'
---
> ORDER BY n.nspname;
>
> \copy (select * from tom_get_pg_class) TO 'tom_pg_class.data'
===================================================================

Note also there's a table name fix in there.

Here are some patches to the README file:

===================================================================
RCS file: README,v
retrieving revision 1.1
diff -u -r1.1 README
--- README    2008/07/30 18:15:20    1.1
+++ README    2008/07/30 18:21:29
@@ -52,27 +52,31 @@
 Actions on Target database:
 ===========================

-5. Create user tables (and load data if required)
+5  Determine the name of the database and schema you intend to use for TOM

-    e.g.
-    psql -f user_tables.sql
+       TOMSCHEMA=tom
+       TESTDB=testtomdb
+       export TOMSCHEMA TESTDB

-6. Create TOM tables
+6. Create user tables (and load data if required)

-    psql -c "create schema tom;"
+    e.g.
+    createdb ${TESTDB}
+    psql -f user_tables.sql -d ${TESTDB}

-    psql -f create_tom_tables.sql --single-transaction -v TOM_SCHEMA='tom'
+6. Create TOM tables

-    Use the current schema
+    psql -c "create schema ${TOMSCHEMA};" -d ${TESTDB}
+    psql -f create_tom_tables.sql --single-transaction -v TOM_SCHEMA='${TOMSCHEMA}' -d ${TESTDB}

 7. Load TOM data into target database

     e.g.
-    psql -f load_data_for_tom.sql --single-transaction  -v TOM_SCHEMA='tom'
+    psql -f load_data_for_tom.sql --single-transaction  -v TOM_SCHEMA='${TOMSCHEMA}' -d ${TESTDB}

 8. Create mapping between target catalog and source stats

-    psql -f get_mapping_for_tom.sql -v TOM_SCHEMA=tom
+    psql -f get_mapping_for_tom.sql -v TOM_SCHEMA=${TOMSCHEMA} -d ${TESTDB}

     We need to be able to match up objects between source and target. This
     script matches up objects that have matching schema and tablenames. If
@@ -80,7 +84,7 @@

 9. Setup TOM plugin

-    LOAD '$libdir/plugins/tom';
+    psql -c "LOAD '\$libdir/tom_hooks';" -d ${TESTDB}

 10. EXPLAIN your queries
-----------------------------

Note, particularly, the change to the plugin name.

As a further inclusion, here's a test script that I wrote up to automate the actions:
--------------------
#!/bin/sh
# $Id$
source ~/dbs/pgsql83.sh env    # A cbbrowne-ism to set up access to my favorite DB cluster...

# These all provide overridable defaults
SOURCEDB=${SOURCEDB:-"postgres"}         # which database does the data come from
SOURCESCHEMA=${SOURCESCHEMA:-"public"}   # which schema is in use (e.g. - what schema to pg_dump)
TESTDB=${TESTDB:-"tomtestdb"}            # target database for TOM test
TOMSCHEMA=${TOMSCHEMA:-"tom"}            # schema to use for TOM tables

psql -d ${SOURCEDB} -c "analyze;"
psql -d ${SOURCEDB} -f get_data_for_tom.sql

dropdb ${TESTDB}
createdb ${TESTDB}
psql -d ${TESTDB} -c "create schema ${TOMSCHEMA};"

pg_dump -n ${SOURCESCHEMA} -s ${SOURCEDB} | psql -d ${TESTDB}

psql -d ${TESTDB} -f create_tom_tables.sql --single-transaction -v TOM_SCHEMA=${TOMSCHEMA}
psql -d ${TESTDB} -f load_data_for_tom.sql --single-transaction -v TOM_SCHEMA=${TOMSCHEMA}
psql -d ${TESTDB} -f get_mapping_for_tom.sql -v TOM_SCHEMA=${TOMSCHEMA}
psql -d ${TESTDB} -c "load '\$libdir/tom_hooks';"
--------------------

It's not clear to me that the plugin is actually working.

When I run EXPLAIN against tables in "tomtestdb", I get results
consistent with an unanalyzed table.  So possibly the "hook" isn't
being used.  Perhaps I'm using it wrongly; perhaps what I have
documented above may suggest to you what's broken.

Mind you, the logs *are* suggesting that they are using the plugin:

LOG:  plugin_get_relation_info relationObjectId = 30026
STATEMENT:  explain analyze select * from test_table ;

That is the OID for test_table.  But here's what I see:

tomtestdb=# explain select * from public.test_table ;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..63.00 rows=1 width=104)
(1 row)

tomtestdb=# \c postgres

That seems consistent with an empty table.  I switch to (on the same
backend) the "source" DB:

You are now connected to database "postgres".
postgres=# explain select * from public.test_table ;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..124.62 rows=6162 width=60)
(1 row)

--
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://cbbrowne.com/info/lsf.html
Rules  of the  Evil Overlord  #145. "My  dungeon cell  decor  will not
feature exposed pipes.  While they add to the  gloomy atmosphere, they
are good  conductors of vibrations and  a lot of  prisoners know Morse
code." <http://www.eviloverlord.com/>