Thread: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Sebastian
Date:
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


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Michael Fuhr
Date:
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


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Sebastian
Date:
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
>


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Michael Fuhr
Date:
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


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Larry Rosenman
Date:
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



Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Sebastian
Date:
> 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
>


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Sebastian
Date:
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
> >
>


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Michael Fuhr
Date:
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


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Tom Lane
Date:
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


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Michael Fuhr
Date:
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


Re: broken 'SHOW TABLE'-like query works in 8, not 8.1.1

From
Sebastian
Date:
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
>