Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Any ideas for a temporary work around? On 12/29/05, Sebastian [EMAIL PROTECTED] wrote: How many columns in the table? There are 4 columns in the table On 12/29/05, Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Changing enable_nestloop works great -- I'll use it for now. Thanks all On 12/30/05, Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] 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_types et
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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
How many columns in the table? There are 4 columns in the table On 12/29/05, Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
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: [EMAIL PROTECTED] Pervasive Software, 12365B Riata Trace Parkway, Austin, TX 78727 Office: 512-231-6173 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
