Re: [PERFORM] [SQL] OFFSET impact on Performance???
Now I read all the posts and I have some answers. Yes, I have a web aplication. I HAVE to know exactly how many pages I have and I have to allow the user to jump to a specific page(this is where I used limit and offset). We have this feature and I cannot take it out. > SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50 Now this solution looks very fast, but I cannot implement it, because I cannot jump from page 1 to page only to page 2. Because I know with this type where did the page 1 ended. And we have some really complicated where's and about 10 tables are involved in the sql query. About the CURSOR I have to read more about them because this is my first time when I hear about. I don't know if temporary tables are a solution, really I don't think so, there are a lot of users that are working in the same time at the same page. So... still DIGGING for solutions. Andy. - Original Message - From: "Ragnar Hafstað" <[EMAIL PROTECTED]> To: Cc: "Andrei Bintintan" <[EMAIL PROTECTED]>; Sent: Thursday, January 20, 2005 9:23 PM Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? On Thu, 2005-01-20 at 19:12 +, Ragnar Hafstað wrote: On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote: > The best way to do pages for is not to use offset or cursors but to use > an > index. This only works if you can enumerate all the sort orders the > application might be using and can have an index on each of them. > > To do this the query would look something like: > > SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50 > > Then you take note of the last value used on a given page and if the > user > selects "next" you pass that as the starting point for the next page. this will only work unchanged if the index is unique. imagine , for example if you have more than 50 rows with the same value of col. one way to fix this is to use ORDER BY col,oid and a slightly more complex WHERE clause as well, of course gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] returning a record from PL/pgSQL
I just tried hard to return a single record fromout a plpgsql-function. While the (otherwise excelent) documentation didn't give me an answer, I found out that this works: select into ret false, balance, balance; return ret; while ret is a composite type. This construction however tastes not good to me. Is there a nicer way? An example for PL/pgSQL which returns not just a scalar but also a composite type should be added to the documentation. i.e. as second part of the RETURN statement. ---(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] still having pg_user error
Tom thank you for the reply. I believe I turned all that off during the install (no firewall). But I can try again. Is there an action I can take now since it is installed and in theory the RPM's are up2date. Sorry I have a few books on postgres Oreily and Sams, but it seems a bit deep on install. At the very least I guess I can do a backup of the database, wipe everything /var/lib/pgsql/data and try the initd thing (verify SELinux is off; not sure how I do that, but I will look at the command you mentioned). I am very happy with my progress thus far (have most of my data coming over), but I am frustrated on my lack of knowledge. I have administered Linux before but its been several years and I don't remember much. I am very impressed with the progress with admin tools and such, I just wish I had a clear idea of the path to get a development postgres box up on fedora and am more then a little worried about administering the production (we ordered a 4 processor Dell with Red HAT, I believe I will also need to install it). Again thank you for that piece of advice, I can always re-install Linux and verify the SELinux option is off, maybe I messed up again. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, January 21, 2005 12:48 AM To: Joel Fradkin Cc: [email protected] Subject: Re: [SQL] still having pg_user error "Joel Fradkin" <[EMAIL PROTECTED]> writes: > Well I re-installed Fedora 3 and updated my postgres and started the = > service > (no command line stuff). > When I connected using pgadmin I still got the sp_user error. Fedora 3? You should've mentioned that before. I'll bet you've got SELinux enabled in enforcement mode. SELinux interferes with initdb and I believe a missing pg_user view is one of the symptoms. This is fixed in the very latest postgresql and selinux-policy-targeted RPMs (u need both), but the path of least resistance is often to dial SELinux down to permissive mode (sudo /usr/sbin/setenforce 0) for long enough to do the initdb. If you're running SELinux in strict rather than targeted policy, good luck ... I honestly haven't tried that one at all ... 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] still having pg_user error
"Joel Fradkin" <[EMAIL PROTECTED]> writes: > Is there an action I can take now since it is installed and in theory the > RPM's are up2date. Sorry I have a few books on postgres Oreily and Sams, but > it seems a bit deep on install. Sure, you can use the RPMs you have. You want something like (as root) service postgresql stop rm -rf /var/lib/pgsql/data setenforce 0 service postgresql start The start script will observe that there's no database directory and proceed to run initdb for you. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] still having pg_user error
Finally I think that did the trick. I did see the GUI editor for security and set SELinux to off and re-booted. I ran the rpm text you gave me I can use pgadmin with no errors, now for another 12 hours of transferring data :( so I can work on the views and stored procedures. This list is a great resource and I appreciate all the ideas comments and help it has made the difference for me. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, January 21, 2005 9:17 AM To: Joel Fradkin Cc: [email protected] Subject: Re: [SQL] still having pg_user error "Joel Fradkin" <[EMAIL PROTECTED]> writes: > Is there an action I can take now since it is installed and in theory the > RPM's are up2date. Sorry I have a few books on postgres Oreily and Sams, but > it seems a bit deep on install. Sure, you can use the RPMs you have. You want something like (as root) service postgresql stop rm -rf /var/lib/pgsql/data setenforce 0 service postgresql start The start script will observe that there's no database directory and proceed to run initdb for you. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] OID's
On Fri, Jan 21, 2005 at 11:17:34AM +0500, Mihail Nasedkin wrote: > I have already read about "System Columns" of the PostgreSQL documentation. > In the table "pg_catalog.pg_attribute" column "attrelid" contain > only "system OID's" but not OID's from records of the user tables. > > But I would like to use OID's of all records of the all my tables. > ^^^^^^ If you want to do that then you'll need to know which tables have OIDs. If you just want rows then you could query pg_class and filter on the relhasoids column, but if you also want things like large objects then you might need to query pg_attribute and look for all columns having an "oid" type. It sounds like you're not interested in the latter, however. > I try to use rules on INSERT action of my tables to store last insert > oid, but at the moment of the INSERT row into table OID value > inaccessible (unknown). A row's OID should be visible in an AFTER trigger. > >> I would like use some SQL queries with the all OID's. > > MF> To what end? Are you aware that PostgreSQL allows tables to be > MF> created without OIDs? > > Yes, of course, but in my case I create tables with OID and then want use > OID of all records of the all tables as one column in some query. Are you aware that OIDs aren't guaranteed to be unique due to wraparound? If you have a UNIQUE constraint on each table's oid column then the combination of (tableoid, oid) might serve your needs. > I think what system of OID's is very useful for application! Assigning row IDs from a common sequence could serve the same purpose, and since sequences are 64 bits you wouldn't be as subject to a wraparound problem (OIDs are 32 bits). > MF> What problem are you trying to solve? > > For example, I want to fetching all rows of the several tables in one > query by means of LEFT JOIN, but not use UNION operator. Again, what problem are you trying to solve? Using OIDs might not be the best solution, and if we knew what you're trying to do then we might be able to suggest alternatives. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] OID's
Mihail Nasedkin wrote: Hello, Michael. Thank you for answer January, 20 2005, 21:48:30: MF> On Thu, Jan 20, 2005 at 03:45:58PM +0500, Mihail Nasedkin wrote: How (where) I can get all OID's of the PostgeSQL installation? In other words where OID's is stored? Is it stored in special table? MF> See the "System Columns" section in the "Data Definition" chapter MF> of the PostgreSQL documentation. Tables that store objects with MF> OIDs should have an oid column; you could query pg_attribute to MF> find out what tables those are. I have already read about "System Columns" of the PostgreSQL documentation. In the table "pg_catalog.pg_attribute" column "attrelid" contain only "system OID's" but not OID's from records of the user tables. But I would like to use OID's of all records of the all my tables. ^^^^^^ I try to use rules on INSERT action of my tables to store last insert oid, but at the moment of the INSERT row into table OID value inaccessible (unknown). You are aware that OIDs aren't guaranteed to be unique, aren't you? I would like use some SQL queries with the all OID's. MF> To what end? Are you aware that PostgreSQL allows tables to be MF> created without OIDs? Yes, of course, but in my case I create tables with OID and then want use OID of all records of the all tables as one column in some query. I think what system of OID's is very useful for application! MF> What problem are you trying to solve? For example, I want to fetching all rows of the several tables in one query by means of LEFT JOIN, but not use UNION operator. Joins are designed to be over primary keys, you should make sure you have valid pkeys defined and use those instead. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] testing/predicting optimization using indexes
I have several questions reagaring the kind of increase in speed I can expect when I use a multi-column index. Here's what I've done so far. I've written some search functions which operate on character varying data used to represent molecular structures. We call this a Smiles string. I want to optimize the search using an index. As a test, I've created 9 integer columns in the tables containting atom counts, e.g. number of carbon atoms, oxygen, aromatic carbon, etc. I then made a multi-column index. Here are some samples times 1. When the table contains only smiles, no 9 integer columns and no index: Select count(smiles) from structure where oe_matches(smiles,'c1c1CC(=O)NC'); 1313 rows in about 15 seconds. 2. When the table contains smiles and the 9 integer columns as an index: Select count(smiles) from structure where oe_matches(smiles,'c1c1CC(=O)NC'); 1313 rows in about 20 seconds. 3. When the table contains smiles and the 9 integer columns as an index: Select smiles,id from structure where (nc,nn,no,ns,"n-arom-c","n-arom-n","n-arom-o","n-arom-s",nhalo) >= (3,1,1,0,6,0,0,0,0) and oe_matches(smiles,'c1c1CC(=O)NC'); 1313 rows in about 7 seconds. I'm quite happy with the speedup in 3, but puzzled over the slowdown in 2. Here are my questions. 1. Why does the search slow down after I've created the extra columns and index, even when I don't ask to use the index in the SQL, as in 2. 2. Since I get such a nice speedup in 3, should I go to the trouble to create a new datatype (smiles) and define how it should be indexed in a way analogous to the 9 integer columns? In other words, could I expect an even greater speedup using a new datatype and index? Thanks, TJ ---(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
