Thread: broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Hi,
I have a query that previously worked fine using pg8 on Fedora. Since
then we've moved to a FreeBSD 6 server running pg8.1.1 and the query
doesn't seem to ever finish.
I have VACUUM ANALYZEd the database. Here is the query:
SELECT column_name, table_schema, table_name, c.data_type,
et.data_type as array_type,
col_description('codes.countries'::regclass,ordinal_position),
c.character_maximum_length FROM information_schema.columns c LEFT JOIN
information_schema.element_typeset ON et.object_schema = table_schema
AND et.object_name = table_name AND et.array_type_identifier = c.dtd_identifier
WHERE table_schema='codes' and table_name='countries' ORDER BY
ordinal_position
-- replaces 'codes' and 'countries' with a schema and table that exist
One fellow on IRC using FreeBSD 4.11 and pg8.1.1 can reproduce the problem.
Any suggestions?
Thanks in advance,
sebastian
On Thu, Dec 29, 2005 at 11:14:59AM -0800, Sebastian wrote: > I have a query that previously worked fine using pg8 on Fedora. Since > then we've moved to a FreeBSD 6 server running pg8.1.1 and the query > doesn't seem to ever finish. How long did you wait? In one of my tests the query took over three times as long to finish in 8.1.1 as it did in 8.0.5, but it did finish. However, EXPLAIN fails in 8.1.1: test=> EXPLAIN SELECT ... ERROR: record type has not been registered Something about the information_schema.element_types view seems to be the problem: test=> EXPLAIN SELECT * FROM information_schema.element_types; ERROR: record type has not been registered -- Michael Fuhr
I've waited 10 minutes before cancelling. On pg8 it runs in less than a second : test=> EXPLAIN SELECT * FROM information_schema.element_types; : ERROR: record type has not been registered I can reproduce this... - sebastian On 12/29/05, Michael Fuhr <mike@fuhr.org> wrote: > On Thu, Dec 29, 2005 at 11:14:59AM -0800, Sebastian wrote: > > I have a query that previously worked fine using pg8 on Fedora. Since > > then we've moved to a FreeBSD 6 server running pg8.1.1 and the query > > doesn't seem to ever finish. > > How long did you wait? In one of my tests the query took over three > times as long to finish in 8.1.1 as it did in 8.0.5, but it did finish. > However, EXPLAIN fails in 8.1.1: > > test=> EXPLAIN SELECT ... > ERROR: record type has not been registered > > Something about the information_schema.element_types view seems to > be the problem: > > test=> EXPLAIN SELECT * FROM information_schema.element_types; > ERROR: record type has not been registered > > -- > Michael Fuhr >
On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: > I've waited 10 minutes before cancelling. On pg8 it runs in less than a second How many columns in the table? In 8.1.1 I'm seeing a nearly exponential increase in time with each extra column, at least up to about five columns; with more columns the time continues to increase although not as sharply. I don't see such an increase in 8.0.5. Querying the views individually doesn't take long; I wonder if the planner is doing something wrong with the join operation. -- Michael Fuhr
On Dec 29 2005, Michael Fuhr wrote: > On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: > > I've waited 10 minutes before cancelling. On pg8 it runs in less than > > a second > > How many columns in the table? In 8.1.1 I'm seeing a nearly > exponential increase in time with each extra column, at least up > to about five columns; with more columns the time continues to > increase although not as sharply. I don't see such an increase in > 8.0.5. Querying the views individually doesn't take long; I wonder > if the planner is doing something wrong with the join operation. > > For clarification, I'm the 4.11-FreeBSD guy refered to above, and with a very simple table, it comes right back with NO results, but I may not have what it's looking for in the table definition. I **DO** get the explain failure, which seems, to me, to be a bug. :( LER -- Larry Rosenman, Database Support Engineer, E-Mail: Larry.Rosenman@pervasive.com Pervasive Software, 12365B Riata Trace Parkway, Austin, TX 78727 Office: 512-231-6173
> How many columns in the table? There are 4 columns in the table On 12/29/05, Michael Fuhr <mike@fuhr.org> wrote: > On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: > > I've waited 10 minutes before cancelling. On pg8 it runs in less than a second > > How many columns in the table? In 8.1.1 I'm seeing a nearly > exponential increase in time with each extra column, at least up > to about five columns; with more columns the time continues to > increase although not as sharply. I don't see such an increase in > 8.0.5. Querying the views individually doesn't take long; I wonder > if the planner is doing something wrong with the join operation. > > -- > Michael Fuhr >
Any ideas for a temporary work around? On 12/29/05, Sebastian <buddhahead@gmail.com> wrote: > > How many columns in the table? > > There are 4 columns in the table > > On 12/29/05, Michael Fuhr <mike@fuhr.org> wrote: > > On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: > > > I've waited 10 minutes before cancelling. On pg8 it runs in less than a second > > > > How many columns in the table? In 8.1.1 I'm seeing a nearly > > exponential increase in time with each extra column, at least up > > to about five columns; with more columns the time continues to > > increase although not as sharply. I don't see such an increase in > > 8.0.5. Querying the views individually doesn't take long; I wonder > > if the planner is doing something wrong with the join operation. > > > > -- > > Michael Fuhr > > >
On Fri, Dec 30, 2005 at 03:15:03AM -0500, Sebastian wrote: > Any ideas for a temporary work around? You could try querying the system catalogs directly instead of using the information_schema views; look at the view definitions and run some "\d" commands under "psql -E" to see what kinds of queries to make. See also "System Information Functions" and "System Catalogs" in the documentation. http://www.postgresql.org/docs/8.1/interactive/functions-info.html http://www.postgresql.org/docs/8.1/interactive/catalogs.html -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> However, EXPLAIN fails in 8.1.1:
> test=> EXPLAIN SELECT * FROM information_schema.element_types;
> ERROR: record type has not been registered
I've applied a patch for this. It's just a bug in EXPLAIN output,
however, and doesn't have anything directly to do with the performance
issue.
regards, tom lane
On Fri, Dec 30, 2005 at 11:02:20AM -0700, Michael Fuhr wrote: > On Fri, Dec 30, 2005 at 03:15:03AM -0500, Sebastian wrote: > > Any ideas for a temporary work around? > > You could try querying the system catalogs directly instead of using > the information_schema views; You could also set enable_nestloop to off for your original query. Now that EXPLAIN is fixed it looks like 8.1.1 is choosing a nested loop where a hash join is actually much faster. -- Michael Fuhr
Changing enable_nestloop works great -- I'll use it for now. Thanks all On 12/30/05, Michael Fuhr <mike@fuhr.org> wrote: > On Fri, Dec 30, 2005 at 11:02:20AM -0700, Michael Fuhr wrote: > > On Fri, Dec 30, 2005 at 03:15:03AM -0500, Sebastian wrote: > > > Any ideas for a temporary work around? > > > > You could try querying the system catalogs directly instead of using > > the information_schema views; > > You could also set enable_nestloop to off for your original query. > Now that EXPLAIN is fixed it looks like 8.1.1 is choosing a nested > loop where a hash join is actually much faster. > > -- > Michael Fuhr >