[SQL] Record size
Firstly an apology - I think this should be posted to the performance mailing list but can anyone tell me how to subscribe to this please? The usual form to fill in is not available on the website. Hopefully others in this list will find the answer of use though. Now the question. Does the number of fields that you have in a record and indeed their size affect performance on a SELECT statement when only a subset of fields is being extracted? So suppose that I have f1 integer f2 varchar(4) f3 varchar(20) f4 text and f4 contains reams of data. (well eg 4k). If I just want f1 and f2, will the performance of the SELECT statement be affected by the fact that f4 is large in physical size? If so, I would be better having two parallel tables one with fields f1,f2,f3 and one with f1,f4 as most of the time I don't want to read in the f4. As a secondary question, presumably it is better to have a permanently compiled view in the database defined as CREATE VIEW myview as SELECT f1,f2,f3 from mytable rather than issuing the query each time direct to the underlying table? Many thanks in advance. Oh, BTW, we are still in the dark ages with v7.0 but will be upgrading. :-) Hilary Hilary Forbes - DMR Computer Limited: http://www.dmr.co.uk/ Direct line: 01689 889950 Switchboard: (44) 1689 86 Fax: (44) 1689 860330 E-mail: [EMAIL PROTECTED] ** ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] find open transactions/locks in 7.2?
> > [select version() --> PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 3.0.4] > > I'm getting hangups every day or so, I presume due to some open transaction > that insert/update/delete'ed on a table that is used by my main app without > a commit. > > Is there some way (in 7.2!) to find who's locking what or who has > a transaction open? > AFAICT No. There has been a thread on a similar topic [HACKERS] Can pessimistic locking be emulated? but it was all referring to 7.3. Anyway, you might want to check. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Record size
Hilary Forbes <[EMAIL PROTECTED]> writes: > Now the question. Does the number of fields that you have in a record and indeed > their size affect performance on a SELECT statement when only a subset of fields is > being extracted? So suppose that I have > f1 integer > f2 varchar(4) > f3 varchar(20) > f4 text > and f4 contains reams of data. (well eg 4k). If I just want f1 and f2, will the > performance of the SELECT statement be affected by the fact that f4 is large in > physical size? If so, I would be better having two parallel tables one with fields > f1,f2,f3 and one with f1,f4 as most of the time I don't want to read in the f4. Most of the possible benefit applies automatically, because large values of f4 will be "toasted" (moved out of line). I don't think it's worth contorting your table structure for. You might care to run some experiments to verify that theory, though. (But update first; experiments against 7.0 don't necessarily prove anything about 7.3 ...) > As a secondary question, presumably it is better to have a permanently compiled view > in the database defined as > CREATE VIEW myview as SELECT f1,f2,f3 from mytable > rather than issuing the query each time direct to the underlying table? There's essentially no difference in performance. Views are not pre-optimized. regards, tom lane ---(end of broadcast)--- TIP 3: 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: [SQL] Record size
Hilary, > Firstly an apology - I think this should be posted to the performance > mailing list but can anyone tell me how to subscribe to this please? The > usual form to fill in is not available on the website. Hopefully others in > this list will find the answer of use though. To: [EMAIL PROTECTED] Re: Message: subscribe pgsql-performance > Many thanks in advance. Oh, BTW, we are still in the dark ages with v7.0 > but will be upgrading. :-) Hilary I'd suggest upgrading first before doing any performance tuning. Postgres performance and tunability has improved worlds since 7.0; it's quite possible that optimization workarounds you make in 7.0 will actually harm performance in 7.3 or 7.4. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Creating Views with Column Names based on Distinct
Hi
CREATE VIEW user_stuff ...select comand that displays what you want... ;
This might be what you want ?:-)
CREATE VIEW user_stuff
SELECT username AS "Username",userpassword AS
"Pass/Attribute",startdate::TEXT AS "Date/Value"
FROM user
UNION SELECT
user.username,userdetail.attributename,userdetail.attributevalue::TEXT
FROM user,userdetail
WHERE user.userid = userdetail.userid
;
Here is some psuedo output :
-- select "Username"s that start with 'j' from view.
-- NOTE: The columns I setup have capitals and 'unsafe' characters so
they must be in double quotes.
SELECT * from user_stuff where "Username" ~ '^j';
Username | Pass/Attribute | Date/Value
--++
joeuser | 5n00py | 01-01-01
joeuser | ju-attribute1 | ju-value1
joeuser | ju-attribute2 | ju-value2
...
janedow | 5eaShe11 | 02-02-02
janedow | jd-attribute1 | jd-value1
janedow | jd-attribute2 | jd-value2
...
NOTE: The the colums all have to be the same type {and probably size}. I
would suggest using TEXT instead of VARCHAR(). Since the data in the
third column is either a date or character data, I cast the date and
value to TEXT so that they would both match.
This looks suprisingly like a radius authentication database, I recently
patched cistron to do PostgreSQL accounting, and will likely make an SQL
authentication patch as well, or switch to freeRadius and help them fix
up there software. I have looked at freeRadius a couple of times, but it
has really bad docs for the SQL support.
Hope this helps.
Guy
Frank Bax wrote:
At 10:59 AM 6/6/03, Damien Dougan wrote:
I was wondering if it is possible to create a table view based on a
table
which is effectively an "attribute list".
For example, suppose I have two tables:
CREATE TABLE user
(
userid integer,
username character varying,
userpassword character varying,
startdate date
);
CREATE TABLE userdetail
(
userid integer,
attributename character varying,
attributevalue character varying
);
Now I want to make a public view of the user, which would have all of
the
defined fields in user, and all of the defined attributes across
userdetail.
I'll think you'll find what you're looking for if you search the
archives of this mailing list for 'crosstab'.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Using a RETURN NEXT
Thanks a lot Gaetano, Joe Now, it is working. --- Mendola Gaetano <[EMAIL PROTECTED]> escribió: > From: "Mr Weinbach, Larry" <[EMAIL PROTECTED]> > > > But at execution time I am getting thi error : > > > > WARNING: Error occurred while executing PL/pgSQL > > function word_case > > WARNING: line 5 at return next > > ERROR: Set-valued function called in context that > > cannot accept a set > > > > I also tried using my own type defined but I got > the > > same error. > > > > Any hint or idea will be appreciated ... > > I guess that you are calling that function in this > way: > #select word_case(); > > that function is a "table function" so you should > use it like a table: > > #select * from word_case(); > > > Regards > Gaetano Mendola > > > > > > > > _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 3: 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
