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

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 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

Any ideas for a temporary work around?

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