Robert Inder <[email protected]> writes: > I have two very similar installations of a system that uses > DBIx::Class to access a fairly small PostgreSQL database -- dozens of > tables,each with hundreds (not thousands) of rows, albeit with a > dozens of foreign key constraints between them. [...] > This same module is present on the other system, but there, the newer > version of DBIx::Class (0.08203) generates a series of completely > different queries against tables in INFORMATION_SCHEMA. These seem to > be coming coming from a function "_table_fk_info()" in > /DBIx/Class/Schema/Loader/DBI/Pg.pm (version 0.07033), and it is these > queries that are taking several minutes to run.
I'm surprised these queries take _that_ long, even with dozens of tables and relationships. Could you try running the query from _table_fk_info manually (both with and without the WHERE clause) in psql and see how long it takes? Type \timing at the psql prompt to turn on timing information. On the schema created by the DBIx::Class::Schema::Loader test suite, which has 54 tables and 39 constraints, it takes about 25ms for a specific table, and 150ms without the where clause. This is on PostgreSQL 9.2.4 on my laptop. Which verson of are you running? > The version of /DBIx/Class/Schema/Loader/DBI/Pg.pm on the "fast" > system is 0.07015: it does not have a "_table_fk_info" function, and > makes no mention of INFORMATION_SCHEMA. > Can anyone shed any light on what is going on? Why the two installations > are going about things in completely different ways? > > Is the system with the newer modules trying to use a new feature that > is horrendously slow? And if so, is there something I can do (e.g to > the database) to make it faster? The newer version of DBIx::Class::Schema::Loader extracts more information about foreign key constraints (specifically ON UPDATE/DELETE and DEFERRABLE clauses), so it runs different queries. On closer inspection (and by running the test suite), it seems like the Pg-specific _table_fk_info function is redundant, and the generic one in DBI.pm suffices. Can you try disabling the former (just change the name of the sub in DBIx/Class/Schema/Loader/DBI/Pg.pm, so the generic one is inherited) and see if that affects the performance? -- - Twitter seems more influential [than blogs] in the 'gets reported in the mainstream press' sense at least. - Matt McLeod - That'd be because the content of a tweet is easier to condense down to a mainstream media article. - Calle Dybedahl _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
