Re: 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
sevcik(at)styxsystems(dot)com
+420 608 782 813

-----Original Message-----
From: Richard Huxton [mailto:dev(at)archonet(dot)com]
Sent: Wednesday, April 09, 2008 6:57 PM
To: Jozef Ševčík
Cc: pgsql-general(at)postgresql(dot)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