[GENERAL] Quoting table/column names vs performance
Hi all, I just switched from MS SQL to PostgreSQL on project and have question about double-quoting names of fields/tables regarding to performance. In MSSQL I had something like: SELECT Column1,Column2 from MyTable In PgSQL I write: SELECT "Column1", "Column2" from "MyTable" Which is fine and working, I have no doubt about it. I'm just guessing if this does not affect performance in any way. I know I may rename tables/fields to lowercase and avoid double-quotting, but it double-quotting has no affect on perf. is it worth it ? Or are there any advantages of using 'non-quoted' identifiers agains double-quoted ? Thanks S pozdravom / Best regards, Jozef Ševčík [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]> +420 608 782 813
Re: [GENERAL] Quoting table/column names vs performance
Richard, thanks for the answer. In fact, I double-quoted identifiers only because PgSQL forced me to do so when using capitalized letters in table/column name. I'm OK with this if it's PgSQL requirement (app runs on NHibernate so I just change column="MyColumn" to column="`MyColumn`" in mapping files). In fact I like capitalized column/table names (more readable for me), but the point is if this affect performance when running queries (for example PgSQL engine might take more time to analyze query with double-quoted identifiers or so). Is there any performance penalty for this ? S pozdravom / Best regards, Jozef Ševčík [EMAIL PROTECTED] +420 608 782 813 -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 09, 2008 5:49 PM To: Jozef Ševčík Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Quoting table/column names vs performance Jozef Ševčík wrote: > Hi all, > > I just switched from MS SQL to PostgreSQL on project and have > question about double-quoting names of fields/tables regarding to > performance. If the cost of quoting column-names is a significant part of your query costs, you must have some very fast queries. I would not worry. > In MSSQL I had something like: SELECT Column1,Column2 from MyTable > > In PgSQL I write: SELECT "Column1", "Column2" from "MyTable" > > Which is fine and working, I have no doubt about it. I'm just > guessing if this does not affect performance in any way. I know I may > rename tables/fields to lowercase and avoid double-quotting, but it > double-quotting has no affect on perf. is it worth it ? Or are there > any advantages of using 'non-quoted' identifiers agains double-quoted If you double-quote identifiers when you create them you'll want to double-quote them everywhere they are used. That's OK with a new sytem, but can be awkward if you have a lot of existing code that isn't already quoted. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quoting table/column names vs performance
Hi Alban, If I do: select * from SourceCategory pgAdmin gives me an error: ERROR: relation "sourcecategory" does not exist If I do: select * from "SourceCategory" It works OK. As Richard mentioned, it's because table was probably created with CREATE TABLE "SourceCategory". I was not sure about this because I had no control over creating the tables. S pozdravom / Best regards, Jozef Ševčík [EMAIL PROTECTED] +420 608 782 813 -Original Message- From: Alban Hertroys [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 09, 2008 7:12 PM To: Jozef Ševčík Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Quoting table/column names vs performance On Apr 9, 2008, at 5:27 PM, Jozef Ševčík wrote: > In MSSQL I had something like: > SELECT Column1,Column2 from MyTable > > In PgSQL I write: > SELECT “Column1”, “Column2” from “MyTable” > > Which is fine and working, I have no doubt about it. I’m just > guessing if this does not affect performance > in any way. What are you trying to fix that you don't just write SELECT Column1,Column2 from MyTable ? Postgres understands that fine, why do you want to quote those identifiers? Maybe your application code is case-sensitive with regards to column (and maybe table) names? Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:806,47fcf8e1927661781427083! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quoting table/column names vs performance
Richard, thanks for the great explanation. I'm sorry because I missed your notes to performance in previous e-mail between the lines. Cast (in)sensitivity is much more clear for me now. When I moved project from MSSQL to Postgres I did not create tables manually, I used some mssql-to-postgres migration tool. So it looks like this tool used to put double-quotes when creating table. So it all depends on how table is exactly created, thank you. A last question - is there any way how to 'switch' this for table without re-creating table again ? Thanks in advance. S pozdravom / Best regards, Jozef Ševčík [EMAIL PROTECTED] +420 608 782 813 -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 09, 2008 6:57 PM To: Jozef Ševčík Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Quoting table/column names vs performance Jozef Ševčík wrote: > Richard, > > thanks for the answer. > In fact, I double-quoted identifiers only because PgSQL forced me to do so > when using capitalized letters in table/column name. Well, if you don't quote them they get folded to lower-case and you get case-insensitive matching. CREATE TABLE MyTable1 (a int); -- ends up as mytable1 CREATE TABLE "MyTable2" (a int); -- stays as MyTable2 SELECT * FROM MyTable1; -- OK, looks for "mytable1" SELECT * FROM MYTABLE1; -- also OK SELECT * FROM MyTaBlE1; -- also OK SELECT * FROM "MyTable1";-- Fails, looks for "MyTable1" SELECT * FROM MyTable2; -- Fails, looks for "mytable2" SELECT * FROM "MyTable2"; -- OK > I'm OK with this if it's PgSQL requirement (app runs on NHibernate so I just > change > column="MyColumn" to column="`MyColumn`" in mapping files). > > In fact I like capitalized column/table names (more readable for me), > but the point is if this affect performance when running queries (for example > PgSQL engine > might take more time to analyze query with double-quoted identifiers or so). > > Is there any performance penalty for this ? As I said, no cost you'll ever notice. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
