Re: [HACKERS] Much Ado About COUNT(*)
I agree with last statement. count(*) is not most important. Most nice thing with index only scan is when it contains more than one column. When there is join among many tables where from each table only one or few columns are taken it take boost query incredibly. For exmaple on when you have customer table and ID, NAME index on it then: select c.name,i.* from customer c, invoice i where c.id=i.customer_id then it is HUGE difference there. without index only scan you require to make index io and random table access (assuming no full scan). With index only scan you need only index scan and can skip expensive random table io. It is very simple but powerful optmization in many cases to reduce join expence on many difficult queries. You can have get some kind of index organized table (you use only index so in fact it is ordered table) Selecting only few columns is quite often scenario in reporting. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jonah H. Harris Sent: Wednesday, January 12, 2005 8:36 PM To: Greg Stark Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Much Ado About COUNT(*) Greg Stark wrote: I think part of the problem is that there's a bunch of features related to these types of queries and the lines between them blur. You seem to be talking about putting visibility information inside indexes for so index-only plans can be performed. But you're also talking about queries like select count(*) from foo with no where clauses. Such a query wouldn't be helped by index-only scans. Perhaps you're thinking about caching the total number of records in a global piece of state like a materialized view? That would be a nice feature but I think it should done as a general materialized view implementation, not a special case solution for just this one query. Perhaps you're thinking of the min/max problem of being able to use indexes to pick out just the tuples satisfying the min/max constraint. That seems to me to be one of the more tractable problems in this area but it would still require lots of work. I suggest you post a specific query you find is slow. Then discuss how you think it ought to be executed and why. You are correct, I am proposing to add visibility to the indexes. As for unqualified counts, I believe that they could take advantage of an index-only scan as it requires much less I/O to perform an index scan than a sequential scan on large tables. Min/Max would also take advantage of index only scans but say, for example, that someone has the following: Relation SOME_USERS user_id BIGINT PK user_nm varchar(32) UNIQUE INDEX some_other_attributes... If an application needs the user names, it would run SELECT user_nm FROM SOME_USERS... in the current implementation this would require a sequential scan. On a relation which contains 1M+ tuples, this requires either a lot of I/O or a lot of cache. An index scan would immensely speed up this query. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Compiere ERP and SQL quirks
Hello We made Compiere (Open source ERP system) to Firebird (Fyracle) This is special version of Firebird with added Oracle compatibility (Oracle PL/SQL). It made porting much easier, but our experience show that it would be now also not very difficult with other databases like PostgreSQL. Compiere contained lot of PL/SQL which size is now largely reduced. Main problem is some SQL constructions which are not supported. Particulary something like this is very important: UPDATE sometable set (col1,col2) = (select col_a,col_b from another_table where ) This construction seems to be quite useful in another cases. Would be it diffcult and possible to add such syntax to PostgreSQL ? Marek Mosiewicz http://www.jotel.com.pl ---(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: [HACKERS] Compiere ERP and SQL quirks
Upps sorry now found it on TODO list. I was not aware that it is SQL92 standard. Is it difficult to implement ? Simplest approach would be to rewrite it to UPDATE t1 set col1 = (select cola ...), col2 = (select colb) but it would result in not optimal plan. Marek Mosiewicz -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure Sent: Friday, January 07, 2005 6:15 PM To: Marek Mosiewicz Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Compiere ERP and SQL quirks Marek Mosiewicz wrote: Hello We made Compiere (Open source ERP system) to Firebird (Fyracle) This is special version of Firebird with added Oracle compatibility (Oracle PL/SQL). It made porting much easier, but our experience show that it would be now also not very difficult with other databases like PostgreSQL. Compiere contained lot of PL/SQL which size is now largely reduced. Main problem is some SQL constructions which are not supported. Particulary something like this is very important: UPDATE sometable set (col1,col2) = (select col_a,col_b from another_table where ) This construction seems to be quite useful in another cases. Would be it diffcult and possible to add such syntax to PostgreSQL ? PostgreSQL has limited support for the SQL 92 row constructor. You can use it in select expressions in most places, but not in update as you noticed. Be forewarned that row constructor expressions involving the or operators can give the wrong answer: select (2,2,3) (2,1,3) returns false when it should return true. Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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
[HACKERS] OLE DB PostgreSQL provider
I'm looking for people who want to join OLE DB Provider project. It is partially finished. I'm looking for people who want to help. -- Marek Mosiewicz [EMAIL PROTECTED] ***r-e-k-l-a-m-a** Chcesz oszczedzic na kosztach obslugi bankowej ? mBIZNES - konto dla firm http://epieniadze.onet.pl/mbiznes ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostgreSQL OLE DB Provider
Do you know any attempts to write native OLE DB provider for PostgreSQL (it would give broader support for VS Net). I would like to write such provider and I want to know if sombody tried it before. Could somebody help me with protocol issues (I have read Backend/Frontend Protocol and studied ODBC driver) Are there any other interesting issues which aren not covered with it. I would like to know how could I implement precompiled statements. Is there any way to send it without parameters to able backend to chache it for future use or it is not necessary. Are there any problems with large objects ? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
