Re: [SQL] two sums in one query
On Friday 08 Jul 2005 12:02 pm, Ramakrishnan Muralidharan wrote: >I have assuemed that the row will having eighter Debit account > or Credit account, the following Query will give sum of debit and > credit accounts > >SELECT SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN > COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN > COALESCE( CREDIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0 > END ) AS CREDIT_AMT FROM actually, all the rows have both 'debit' and 'credit', but based on the hint you gave i made this query which works: select sum(case when debit=account then amount else 0 end) as debtotal, sum(case when credit=account then amount else 0 end) as credtotal from voucherrows where debit = account or credit = account thanks for taking the trouble - i never believed this was possible, just asked on the off chance that it *may* be possible -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(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
Re: [SQL] getting back autonumber ... Another MsSQL Comparation Question
Hi Listers, I need to know the number of affected (Inserted, deleted, updated) rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT @@IDENTITY. Is there any @@RowCount similar statement in PostGres?? Alessandro - Liga One Sistemas [EMAIL PROTECTED] - Original Message - From: "Alvaro Herrera" <[EMAIL PROTECTED]> To: "mail TechEvolution" <[EMAIL PROTECTED]> Cc: Sent: Thursday, July 07, 2005 3:03 PM Subject: Re: [SQL] getting back autonumber just inserted > On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote: > > hello > > > > i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0 > > (windowsinstaller) on a xp prof platform > > > > i would like to get back the autonumber from the last record inserted, > > for other SQL db (m$ sql db ...) i could use: > > SELECT @@ IDENTITY > > > > can someone help me by informing me what the SQL syntax is to be used > > with PostGreSQL db and get the same result, the last autonumber inserted? > > You use the currval() function, using the name of the involved sequence > as parameter. There is a pg_get_serial_sequence() function, to which > you give the table name and column name, and it will give you the > sequence name. > > -- > Alvaro Herrera () > Oh, oh, las chicas galacianas, lo harán por las perlas, > ¡Y las de Arrakis por el agua! Pero si buscas damas > Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck) > > ---(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 > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] getting back autonumber ... Another MsSQL Comparation Question
[Please start a new thread when asking new questions.] On Fri, Jul 08, 2005 at 08:56:56AM -0300, jimmy.olsen wrote: > I need to know the number of affected (Inserted, deleted, updated) > rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT > @@IDENTITY. Is there any @@RowCount similar statement in PostGres?? Your client interface should have a function to get the row count. In libpq, for example, you can call PQcmdTuples(); in PL/pgSQL you can use GET DIAGNOSTICS. See the documentation for whatever interface you're using. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] getting back autonumber just inserted
On Fri, Jul 08, 2005 at 05:03:37 +0200, PFC <[EMAIL PROTECTED]> wrote: > > It's the first time I see a MySQLism in postgres ! This has meaning in more ways than one. > However I like it, cos it might subvert some MySQL users, and > provide easy answers to The Weekly Question on the mailing list (ie > where > is AUTO_INCREMENT) ? More likely people will shoot themselves in the foot with this feature and come to the mailing list with questions that take even more time to figure out than telling them how to use currval(pg_get_serial_sequence(). IMO it is a bad idea to include this feature and that no sane developer will make use of it in applications. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] two sums in one query
SELECT SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN COALESCE( CREDIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS CREDIT_AMT FROM I don't know if it will use indexes (bitmapped OR indexes in 8.1 ?)... if you have indexes on debit and on credit, you could do SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT sum(amount) FROM table WHERE credit=x) AS credit; actually, all the rows have both 'debit' and 'credit', but based on the hint you gave i made this query which works: select sum(case when debit=account then amount else 0 end) as debtotal, sum(case when credit=account then amount else 0 end) as credtotal from voucherrows where debit = account or credit = account thanks for taking the trouble - i never believed this was possible, just asked on the off chance that it *may* be possible ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] two sums in one query
On Fri, Jul 08, 2005 at 15:49:20 +0200, PFC <[EMAIL PROTECTED]> wrote: > > > >> SELECT SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN > >>COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN > >>COALESCE( CREDIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0 > >>END ) AS CREDIT_AMT FROM > > I don't know if it will use indexes (bitmapped OR indexes in 8.1 > ?)... if you have indexes on debit and on credit, you could do > > SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT > sum(amount) FROM table WHERE credit=x) AS credit; If most of the records are credits or debits you don't want to do this. A single sequential scan through the table will be the best plan. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Make COUNT(*) Faster?
Chris Browne wrote: > None of those transactions have COMMITted, so there are some 78 tuples > "in limbo" spread across 16 transactions. > > If there were some "single secret place" with a count, how would you > suggest it address those 78 tuples and 16 transactions that aren't yet > (and maybe never will be) part of the count? Hmmm, I understand this and don't doubt it, but out of curiousity, how does the current SELECT COUNT(*) handle this? It doesn't lock the entire table while counting (I assume) so the current implementation is really just an approximate count in the above scenario anyway. Or even when not, since the true 'count' is likely to have changed by the time the user does anything with the result of SELECT COUNT(*) on any active table (and on an inactive table, pg_class.reltuples is nearly as good as SELECT COUNT(*) and far faster to get to.) I assume this has been beaten well past death, but I don't see why it wouldn't be possible to keep pg_class.reltuples a bit more up-to-date instead of updating it only on vacuums. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make COUNT(*) Faster?
On Fri, Jul 08, 2005 at 07:12:26 -0700, Steve Wampler <[EMAIL PROTECTED]> wrote: > > Hmmm, I understand this and don't doubt it, but out of curiousity, how > does the current SELECT COUNT(*) handle this? It doesn't lock the entire It only counts tuples visible to the current transaction. > table while counting (I assume) so the current implementation is really > just an approximate count in the above scenario anyway. Or even when No, it is an exact count. > not, since the true 'count' is likely to have changed by the time the There is no single true count. There is a separate true count for each transaction. > user does anything with the result of SELECT COUNT(*) on any active table > (and on an inactive table, pg_class.reltuples is nearly as good as > SELECT COUNT(*) and far faster to get to.) > > I assume this has been beaten well past death, but I don't see why it > wouldn't be possible to keep pg_class.reltuples a bit more up-to-date > instead of updating it only on vacuums. Because it costs resources to keep track of that and people don't usually need exact tuple counts for whole tables. Those that do and are willing to pay the price can use triggers to maintain a count in a separate table. ---(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
Re: [SQL] two sums in one query
SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT sum(amount) FROM table WHERE credit=x) AS credit; If most of the records are credits or debits you don't want to do this. A single sequential scan through the table will be the best plan. I thought that debit = source account # and credit = dest account #, and there are a lot of different account... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Make COUNT(*) Faster?
Bruno Wolff III wrote: > No, it is an exact count. Yes, for the transaction, but it's an approximation of the number of tuples in the table - which is probably what the people who worry about its cost are more interested in (an approximate count for the table). I'm also claiming that a true count for any active table is meaningless and am *not* suggesting that effort be spent on trying to produce such a true count. >>I assume this has been beaten well past death, but I don't see why it >>wouldn't be possible to keep pg_class.reltuples a bit more up-to-date >>instead of updating it only on vacuums. > > > Because it costs resources to keep track of that and people don't usually need > exact tuple counts for whole tables. Yes, we agree completely! (Which is why I said 'a bit more' instead of 'exactly' above.) My uses for COUNT(*) are to get 'reasonable' approximate counts of the table sizes - not true counts, but approximate values. Unfortunately, pg_class.reltuples gets too far off too fast for me to use it as a consistent guide to current table size. If you Folks Who Know believe that simply keeping pg_class.reltuples 'closer' to the actual table size is too expensive, I'll accept that [after all, I have to right now anyway], but I'm surprised that it is, given all the other work that must go on at the start/close of a transaction. I also understand that 'reasonable' and 'closer' are vague terms. In the example scenerio where there were around 80 rows in an indeterminate state, my claim is that, in a table of around a million rows, it doesn't matter whether some portion of those indeterminate rows are included in an approximation of the table size or not (though it might in a table of 100 'true' rows - but the decision to ask for a true 'transaction' count (slow) or an approximate table size (fast) should be left to the user in either case). So, leave COUNT(*) alone. But it would be very handy to have a way to get an approximate table size that is more accurate than is provided by a pg_class.reltuples that is only updated on vacuums. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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
Re: [SQL] Make COUNT(*) Faster?
> So, leave COUNT(*) alone. But it would be very handy to have a > way to get an approximate table size that is more accurate than is > provided by a pg_class.reltuples that is only updated on vacuums. Create 2 sequences, one for counting tuple additions and one for counting tuple deletions. When you INSERT a tuple, bump the "added" sequence (select nextval()); When you DELETE a tuple, bump the "deleted" sequence (select nextval()); To retrieve an approximate count, take the current value of both sequences (select directly -- don't use currval) and subtract the "deletes" from the "adds". This is a very fast tracking mechanism with the catch that it does not handle rollbacks -- but you only wanted approximate. Put all of the logic inside a pair of triggers and a function within the DB. -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Make COUNT(*) Faster?
On 7/8/05, Steve Wampler <[EMAIL PROTECTED]> wrote: > > None of those transactions have COMMITted, so there are some 78 tuples > > "in limbo" spread across 16 transactions. > > > > If there were some "single secret place" with a count, how would you > > suggest it address those 78 tuples and 16 transactions that aren't yet > > (and maybe never will be) part of the count? > > Hmmm, I understand this and don't doubt it, but out of curiousity, how > does the current SELECT COUNT(*) handle this? It doesn't lock the entire > table while counting (I assume) so the current implementation is really > just an approximate count in the above scenario anyway. Or even when > not, since the true 'count' is likely to have changed by the time the > user does anything with the result of SELECT COUNT(*) on any active table > (and on an inactive table, pg_class.reltuples is nearly as good as > SELECT COUNT(*) and far faster to get to.) > > I assume this has been beaten well past death, but I don't see why it > wouldn't be possible to keep pg_class.reltuples a bit more up-to-date > instead of updating it only on vacuums. Use EXPLAIN SELECT * FROM yourcountedtable; Planner seems to track estimated statistics on-the-fly. :) You can even wrap EXPLAIN SELECT in a pgsql function if you need it. Regards, Dawid PS: And be aware that these are 'statistics'. And the statement that there are lies, big lies and statistics is sometimes true even for PostgreSQL. ;-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Make COUNT(*) Faster?
On Fri, Jul 08, 2005 at 08:07:27AM -0700, Steve Wampler wrote: > Bruno Wolff III wrote: > > No, it is an exact count. > > Yes, for the transaction, but it's an approximation of the number of > tuples in the table - which is probably what the people who worry about > its cost are more interested in (an approximate count for the table). You seem to have the wrong idea of "tuples in the table" here. You need to think harder about MVCC visibility rules. Given MVCC, there isn't really a "view from nowhere" in the system -- there's just the idea of what tuple visibility. For a little more, you might want to look at the presentation Tom Lane made for this: http://www.postgresql.org/files/developer/transactions.pdf A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Make COUNT(*) Faster?
On 7/8/05, Rod Taylor <[EMAIL PROTECTED]> wrote: > Create 2 sequences, one for counting tuple additions and one for > counting tuple deletions. > > When you INSERT a tuple, bump the "added" sequence (select nextval()); > > When you DELETE a tuple, bump the "deleted" sequence (select nextval()); > > To retrieve an approximate count, take the current value of both > sequences (select directly -- don't use currval) and subtract the > "deletes" from the "adds". Never thought of that! Good idea. :-) Regards, Dawid PS: There aren't any on ROLLBACK triggers, right? ;-))) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make COUNT(*) Faster?
Steve Wampler <[EMAIL PROTECTED]> writes: > So, leave COUNT(*) alone. But it would be very handy to have a > way to get an approximate table size that is more accurate than is > provided by a pg_class.reltuples that is only updated on vacuums. If you want something cheap, you could use the same technique the planner uses nowadays: take RelationGetNumberOfBlocks() (which is guaranteed accurate) and multiply by reltuples/relpages. I don't see anyplace where RelationGetNumberOfBlocks is directly exposed to users now, but it'd be trivial to code up a couple of C functions to provide this functionality. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make COUNT(*) Faster?
On Fri, 2005-07-08 at 17:34 +0200, Dawid Kuroczko wrote: > On 7/8/05, Rod Taylor <[EMAIL PROTECTED]> wrote: > > Create 2 sequences, one for counting tuple additions and one for > > counting tuple deletions. > > > > When you INSERT a tuple, bump the "added" sequence (select nextval()); > > > > When you DELETE a tuple, bump the "deleted" sequence (select nextval()); > > > > To retrieve an approximate count, take the current value of both > > sequences (select directly -- don't use currval) and subtract the > > "deletes" from the "adds". > > Never thought of that! Good idea. :-) > >Regards, > Dawid > > PS: There aren't any on ROLLBACK triggers, right? ;-))) No. You could make then RI triggers and have them deferred until commit though. -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] two sums in one query
On Fri, Jul 08, 2005 at 16:49:44 +0200, PFC <[EMAIL PROTECTED]> wrote: > > >>SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT > >>sum(amount) FROM table WHERE credit=x) AS credit; > > > >If most of the records are credits or debits you don't want to do this. > >A single sequential scan through the table will be the best plan. > > I thought that debit = source account # and credit = dest account #, > and there are a lot of different account... You aren't likely to see a significant speed up in that case either. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make COUNT(*) Faster?
Tom Lane wrote: > Steve Wampler <[EMAIL PROTECTED]> writes: > >>So, leave COUNT(*) alone. But it would be very handy to have a >>way to get an approximate table size that is more accurate than is >>provided by a pg_class.reltuples that is only updated on vacuums. > > If you want something cheap, you could use the same technique the > planner uses nowadays: take RelationGetNumberOfBlocks() (which is > guaranteed accurate) and multiply by reltuples/relpages. I don't > see anyplace where RelationGetNumberOfBlocks is directly exposed to > users now, but it'd be trivial to code up a couple of C functions to > provide this functionality. Yes - this would be an excellent approximation for my needs! The solution that Dawid Kuroczko suggested (just call "explain select * on ..." and parse the result) would be equivalent these days, right? (I think in the 7.x versions the planner was just using pg_class.reltuples, which wouldn't have helped.) If true, I can handle that parsing myself easily enough without exposing RelationGetNumberOfBlocks. Thanks (Tom and Dawid)! -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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
Re: [SQL] Make COUNT(*) Faster?
which wouldn't have helped.) If true, I can handle that parsing myself easily enough without exposing RelationGetNumberOfBlocks. Is there a way to get EXPLAIN results in a non-text-formatted way for easier use ? I'm asking, because it seems the feature set grows by the minute in posgres nowadays... ---(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
[SQL] Index creation question for expression (col1 || '-' || col2)
Greetings, A question about creating index for the following expression. CREATE INDEX idx_t1 ON test (col1 || '-' || col2); May I know is it possible and how I may create index for the expression such as "col1 || '-' || col2" for a table please? Thanks a lot, Emi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Index creation question for expression (col1 || '-' || col2)
Ying Lu <[EMAIL PROTECTED]> writes: > A question about creating index for the following expression. > CREATE INDEX idx_t1 ON test (col1 || '-' || col2); You need more parentheses: CREATE INDEX idx_t1 ON test ((col1 || '-' || col2)); regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Make COUNT(*) Faster?
Steve Wampler <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> If you want something cheap, you could use the same technique the >> planner uses nowadays: take RelationGetNumberOfBlocks() (which is >> guaranteed accurate) and multiply by reltuples/relpages. > Yes - this would be an excellent approximation for my needs! The > solution that Dawid Kuroczko suggested (just call "explain select * > on ..." and parse the result) would be equivalent these days, right? Close enough (the planner actually does some additional heuristic stuff to avoid going crazy on corner cases). 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
Re: [SQL] Index creation question for expression (col1 || '-' || col2)
On Fri, Jul 08, 2005 at 12:08:41PM -0400, Ying Lu wrote: > > CREATE INDEX idx_t1 ON test (col1 || '-' || col2); > > May I know is it possible and how I may create index for the expression > such as "col1 || '-' || col2" for a table please? See "Indexes on Expressions" in the documentation: http://www.postgresql.org/docs/8.0/static/indexes-expressional.html "The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example" That second example is exactly what you're trying to do: CREATE INDEX people_names ON people ((first_name || ' ' || last_name)); -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Make COUNT(*) Faster?
> I'm also claiming that a true count for any active table is
> meaningless and am *not* suggesting that effort be spent on trying
> to produce such a true count.
That's a pretty big assumption that would in fact be WRONG.
We have managers interested in counting the number of objects we have
around (As a domain registry, what objects would you imagine those
might be :-)), and they're keen on possibly even being able to
reconcile those counts from day to day based on transaction activity.
Leaping into some sort of vague guesstimation would destroy the
ability to do any kind of analysis of activity, and I daresay enrage
them.
There may be times that a really rough guess can suffice; there are
other times when exactness is absolutely vital.
Creating a "fast but WRONG COUNT(*)" which prevented getting the exact
answer that the present implementation provides would be a severe
misfeature.
--
output = reverse("gro.gultn" "@" "enworbbc")
http://linuxdatabases.info/info/rdbms.html
"The test of a principle is whether it applies even to people you
don't like." -- Henry Spencer
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Index creation question for expression (col1 || '-' || col2)
On Fri, Jul 08, 2005 at 12:08:41 -0400, Ying Lu <[EMAIL PROTECTED]> wrote: > Greetings, > > A question about creating index for the following expression. > > CREATE INDEX idx_t1 ON test (col1 || '-' || col2); > > May I know is it possible and how I may create index for the expression > such as "col1 || '-' || col2" for a table please? You have already received some answers to this specific question. However, you might consider a different solution to the underlying problem. You can make a multicolumn index that for many purposes would be better than the above functional index. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Index creation question for expression (col1 || '-' ||
On Fri, Jul 08, 2005 at 12:08:41PM -0400, Ying Lu wrote: CREATE INDEX idx_t1 ON test (col1 || '-' || col2); May I know is it possible and how I may create index for the expression such as "col1 || '-' || col2" for a table please? "The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example" That second example is exactly what you're trying to do: CREATE INDEX people_names ON people ((first_name || ' ' || last_name)); This is exactly what I want. I missed a pair of "()" :( . That is why I got an error. Thank you very much. - Emi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make COUNT(*) Faster?
Christopher Browne wrote: >>I'm also claiming that a true count for any active table is >>meaningless and am *not* suggesting that effort be spent on trying >>to produce such a true count. > > > That's a pretty big assumption that would in fact be WRONG. Please reread the message from Bruno and reconcile the above statement with his assertion (which I believe) that there is *no* single true count for an active table. [I'm defining 'active' as currently undergoing insert/copy/delete/update actions]. > We have managers interested in counting the number of objects we have > around (As a domain registry, what objects would you imagine those > might be :-)), and they're keen on possibly even being able to > reconcile those counts from day to day based on transaction activity. If Bruno is correct, then they need to do this reconcilation from within a single transaction (the same one that does the COUNT(*)) - or else they are working on an 'inactive' table [one not currently accepting changes]. If neither condition holds, then isn't the result they are using from COUNT(*) currently is *already* an approximation? > Leaping into some sort of vague guesstimation would destroy the > ability to do any kind of analysis of activity, and I daresay enrage > them. No doubt! Let's hope the above conditions hold. > There may be times that a really rough guess can suffice; there are > other times when exactness is absolutely vital. But, as others have said, COUNT(*) does not return a true count for a table, but rather just a true count for the *current transaction*. So COUNT(*)'s from different simultaneous transactions may very well produce different values. > Creating a "fast but WRONG COUNT(*)" which prevented getting the exact > answer that the present implementation provides would be a severe > misfeature. Agreed - note that I did not suggest replacing the current COUNT(*) with an inexact version, but wanted (and now have) a quick way to get a reasonable approximation of the current table size. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Make COUNT(*) Faster?
On Fri, Jul 08, 2005 at 06:08:03PM +0200, PFC wrote: > > > >which wouldn't have helped.) If true, I can handle that parsing myself > >easily enough without exposing RelationGetNumberOfBlocks. > > Is there a way to get EXPLAIN results in a non-text-formatted way > for easier use ? > I'm asking, because it seems the feature set grows by the minute in > posgres nowadays... Not yet, but a friend of mine was working on a patch to make it output in a custom XML format, to be able to create a tool similar to Redhat's Visual Explain. I expect he will show up in pgsql-hackers sometime ... In spanish: http://www.ubiobio.cl/~gpoo/weblog/archives/000397.html -- Alvaro Herrera () "Find a bug in a program, and fix it, and the program will work today. Show the program how to find and fix a bug, and the program will work forever" (Oliver Silfridge) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Clustering problem
I have what I call a "dictionary" table which supports a "master table". This dictionary table is designed to hold generic data : CREATE TABLE sup_data (link_id uniqueidentifier, field_name varchar(255), field_data text) WITH OIDS; ... It works well when you're digging into it to pull the supplementary information for a small number of rows in the master table. It uses an index on the link_id, and can jump right to the few pages for the supplemental data. That was the design. Now "the powers that be" want to do some aggreate inquiries on subsets of the generic data, based on many rows from the master table. This doesn't work so well... Its having to pull many pages to create the result set to aggreate on. If I could cluster the generic data to match the clustering on the "master table" it would reduce the number of pulled pages considerably and the speedup would make it work well. I'm trying to avoid replicating the column and index used to cluster the main table in this dictionary table. Is it even possible to cluster a table based on the clustering scheme (which is not the link_id ...) from the master table? Can you gurus think of a better strategy? :) (Please??) :) CG Sell on Yahoo! Auctions no fees. Bid on great items. http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Clustering problem
Is it even possible to cluster a table based on the clustering scheme (which is not the link_id ...) from the master table? Can you gurus think of a better strategy? :) (Please??) :) You can create a functional index on a function which returns the desired order by looking in the main table, cluster it, then drop the index... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
