Re: selecting data from information_schema.columns - Mailing list pgsql-performance

Hi

Thanks for you replies.

Regarding, newsysviews, what is the current state, I have had a quick
look at the pgFoundry site and the last updates were 9 months ago.

The most efficient way in the short term I can find to improve
performance for our application is to create a table from
information_schema.columns and update it when tables a created or
deleted, or columns added or removed. E.g.

=>  create table my_information_schema_columns as select * from
information_schema.columns;
=>  create index my_information_schema_columns_index
on my_information_schema_columns (table_name);

Update table with the following statements:

When tables or columns are added:
=>  insert into my_information_schema_columns select * from
information_schema.columns
->  except select * from my_information_schema_columns;

When tables are removed, does not work for column changes:
=>  delete from my_information_schema_columns
->  where table_name = (select table_name from my_information_schema_columns
->   except select table_name from information_schema.columns);

For column changes a script will need to be created, the following
returns the rows to be deleted.  (Any alternative ideas?)
=> select table_name, column_name, ordinal_position from
my_information_schema_columns
-> except select table_name, column_name, ordinal_position from
information_schema.columns;


My problem now is how to get the update statements to be executed when a
table is created or dropped, or columns are added or removed.  For our
application, this is not very often.  My understanding is that triggers
cannot be created for system tables, therefore the updates cannot be
triggered when pg_tables is modified.  Also how to detect column changes
is a problem.

Detecting when a table has been added is relatively easy and can be
performed by our application, e.g. check my_information_schema_columns,
if it does not exist, check information_schema.columns, if exist, run
update statements.

A simple method would be to run a cron job to do the updates, but I
would like to try to be a bit more intelligent about when  the update
statements are executed.

Regards
Steve Martin


Jim C. Nasby wrote:

>On Sun, Oct 01, 2006 at 11:01:19PM -0400, Tom Lane wrote:
>
>
>>Steve Martin <smartin@nec.co.nz> writes:
>>
>>
>>>I am trying to determine if there is a way to improve the performance
>>>when selecting data from the information_schema.columns view.
>>>
>>>
>>In my experience, there isn't any single one of the information_schema
>>views whose performance doesn't suck :-(.  Somebody should work on that
>>sometime.  I haven't looked closely enough to determine where the
>>bottlenecks are.
>>
>>
>
>Looking at the newsysviews stuff should prove enlightening...
>AndrewSN spent a lot of time making sure those views are very
>performant.
>
>

--
               \\|//             From near to far,
                @ @              from here to there,
        ---oOOo-(_)-oOOo---      funny things are everywhere.  (Dr. Seuss)