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

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
sevcik@styxsystems.com
+420 608 782 813


-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
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

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

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
sevcik@styxsystems.com
+420 608 782 813


-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
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

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:737,47fcf8df927661984376163!

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
sevcik@styxsystems.com
+420 608 782 813


-----Original Message-----
From: Alban Hertroys [mailto:dalroi@solfertje.student.utwente.nl]
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!

Jozef Ševčík wrote:
> 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 ?
>
ALTER TABLE "MixedCase" RENAME TO "lowercase";

The double quotes are optional on all lower case identifiers.

The tables you're dealing with are not, as far as I know, specially
flagged as case sensitive or "needs double quotes". They just happen to
have names containing upper case characters. As PostgreSQL flattens
unquoted identifiers to lowercase, that means that you need to double
quote them. There's nothing special about TableName vs tablename vs
TABLENAME, at least as far as I know.

You can double quote all identifiers and I think many automated query
building tools do just that. It's just a pain to type.

--
Craig Ringer

On Wed, 9 Apr 2008 19:11:57 +0200
Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

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

There are a couple of small gotcha.

eg.

- PHP actually is case sensitive so:

$row['MyRow'] != $row['myrow']

If you used camel case in pg without quotes it will be a pain.

- Some pg functions aren't case-proof eg. pg_get_serial_sequence

I'm thinking to write a script to quote all identifiers... but I'm
worried it will look to much as a parser rather than a simple sed
script since I got trapped by the above too.


--
Ivan Sergio Borgonovo
http://www.webthatworks.it