Re: [PERFORM] How can fixed and variable width columns perform similarly?
Siddharth Anand [EMAIL PROTECTED] writes: How can a field that doesn't have a limit like text perform similarly to char varying(128), for example? At some point, we need to write data to disk. The more data that needs to be written, the longer the disk write will take, especially when it requires finding free sectors to write to. What's your point? If you're not going to put more than 128 characters in the field, there's no difference in the amount of data involved. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] How can fixed and variable width columns perform similarly?
I think the manual is implying that if you store a value like Sid in a field either of type varchar(128) or type text there is no performance difference. The manual is not saying that you get the same performance storing a 500k text field as when you store the value Sid. Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Siddharth Anand Sent: Friday, April 27, 2007 10:32 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] How can fixed and variable width columns perform similarly? Hi! I read the link below and am puzzled by or curious about something. http://www.postgresql.org/docs/8.1/interactive/datatype-character.html The Tip below is intriguing Tip: There are no performance differences between these three types, apart from the increased storage size when using the blank-padded type. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead. How can a field that doesn't have a limit like text perform similarly to char varying(128), for example? At some point, we need to write data to disk. The more data that needs to be written, the longer the disk write will take, especially when it requires finding free sectors to write to. Another interesting quote from the same page is the following: Long values are also stored in background tables so they do not interfere with rapid access to the shorter column values. If the long values are stored in a separate table, on a different part of the disk, doesn't this imply an extra disk seek? Won't it therefore take longer? Sid ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How can fixed and variable width columns perform similarly?
Hi Tom, My question wasn't phrased clearly. Oracle exhibits a performance degradation for very large-sized fields (CLOB types that I equate to PostGres' text type) when compared with the performance of field types like varchar that handle a max character limit of a few thousand bytes in Oracle. It sounds like PostGres doesn't exhibit this same difference. I wanted to understand how this could be and whether there was a trade-off. Cheers! Sid Siddharth Anand [EMAIL PROTECTED] writes: How can a field that doesn't have a limit like text perform similarly to char varying(128), for example? At some point, we need to write data to disk. The more data that needs to be written, the longer the disk write will take, especially when it requires finding free sectors to write to. What's your point? If you're not going to put more than 128 characters in the field, there's no difference in the amount of data involved. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] How can fixed and variable width columns perform similarly?
Siddharth Anand [EMAIL PROTECTED] writes: My question wasn't phrased clearly. Oracle exhibits a performance degradation for very large-sized fields (CLOB types that I equate to PostGres' text type) when compared with the performance of field types like varchar that handle a max character limit of a few thousand bytes in Oracle. It sounds like PostGres doesn't exhibit this same difference. I wanted to understand how this could be and whether there was a trade-off. Ah. Well, the answer is that we change behavior dynamically depending on the size of the particular field value, instead of hard-wiring it to the declared column type. It sounds like Oracle's CLOB might be doing about the same thing as an out-of-line toasted field value in Postgres. In PG, text and varchar behave identically except that varchar(N) adds an insert-time check on the length of the field value --- but this is just a constraint check and doesn't have any direct influence on how the value is stored. regards, tom lane ---(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
