Re: [HACKERS] Proposal: Multiversion page api (inplace upgrade)
Gregory Stark wrote: > "Tom Lane" <[EMAIL PROTECTED]> writes: > > > (Likely counterexample: adding collation info to text values.) > > I don't think the argument really needs an example, but I > would be pretty > upset if we proposed tagging every text datum with a > collation. Encoding > perhaps, though that seems like a bad idea to me on > performance grounds, but > collation is not a property of the data at all. Again not directly related to difficulties upgrading pages... The recent discussion ... http://archives.postgresql.org/pgsql-hackers/2008-06/msg00102.php ... mentions keeping collation information together with text data, however it is referring to keeping it together when processing it, not when storing the text. Regards, Stephen Denne. -- At the Datamail Group we value teamwork, respect, achievement, client focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. The Datamail Group, through our GoGreen programme, is committed to environmental sustainability. Help us in our efforts by not printing this email. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Core team statement on replication in PostgreSQL
Hannu Krosing wrote: > The simplest form of synchronous wal shipping would not even need > postgresql running on slave, just a small daemon which > reports when wal > blocks are a) received and b) synced to disk. While that does sound simple, I'd presume that most people would want the guarantee of the same version of postgresql installed wherever the logs are ending up, with the log receiver speaking the same protocol version as the log sender. I imagine that would be most easily achieved through using something like the continuously restoring startup mode of current postgresql. However variations on this kind of daemon can be used to perform testing, configuring it to work well, go slow, pause, not respond, disconnect, or fail in particular ways, emulating disk full, etc. Regards, Stephen Denne. -- At the Datamail Group we value teamwork, respect, achievement, client focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. The Datamail Group, through our GoGreen programme, is committed to environmental sustainability. Help us in our efforts by not printing this email. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] triggers on prepare, commit, rollback... ?
> Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > >>>> trigger on "prepare", "commit", "rollback", "savepoint", > >>> This is a sufficiently frequently asked question that I > wish someone > >>> would add an entry to the FAQ about it, or add it to the > TODO list's > >>> "Features we don't want" section. > > > >> OK, remind me why we don't want it again? > > > > I'm sure I've ranted on this several times before, but a > quick archive > > search doesn't find anything. I know of this very short "rant": http://archives.postgresql.org/pgsql-hackers/2008-04/msg01200.php Florian G. Pflug wrote: > A possible use-case for that is aggregating some statistics collected > during a transaction. One could e.g. maintain a cache of > table rowcounts > by summing up the number of inserted and deleted records per > table with > some per-row ON INSERT and ON DELETE (presumably C-language) triggers, > and than update a global cache at transaction end. This is possible now using deferred constraint triggers (PL/pgSQL is sufficient), though better described IMHO as "before prepare" or "before commit" rather than "on ...". Any FAQ addition should mention deferred constraint triggers. I would expect problems with "after commit" and "after rollback" triggers. I think that the documentation of when the existing deferred constraint triggers run in 2PC/non 2PC could be clarified, and the effects on the transaction state that are possible within such trigger functions documented. http://www.postgresql.org/docs/current/interactive/sql-createconstraint.html Says "They can be fired either at the end of the statement causing the triggering event, or at the end of the containing transaction;" It refers to http://www.postgresql.org/docs/current/interactive/sql-createtable.html Which says "Checking of constraints that are deferrable can be postponed until the end of the transaction" and "If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction." I'd also contest this statement: "Only foreign key constraints currently accept this clause. All other constraint types are not deferrable." Regards, Stephen Denne. At the Datamail Group we value teamwork, respect, achievement, client focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. The Datamail Group, through our GoGreen programme, is committed to environmental sustainability. Help us in our efforts by not printing this email. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
> > The big gotcha is that these are all non-transactional > > : if you rollback, > > GD and SD stay the same, and when you issue a query, you can > > assume the > > state of SD and GD is random (due to previous queries) unless you > > initialize them to a known value. > > Using txid_current() as a key should alleviate that. No... hold on, it is per session, and a session can't have two or more transactions active at once can it? (Though I hear of things called sub-transactions) So the problem is that other functions may be using GD themselves, and your own code is at the mercy of the other functions. Conversely you shouldn't clear GD, as some other function may be using it. So you're better off using a single function for everything, and using SD within it? There isn't any way of telling whether the function is being called for the first time in a transaction. You don't know when to clear it. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
PFC wrote > <[EMAIL PROTECTED]> wrote: > > I don't know plpythonu (nor python), just read a few docs now: > > Learn Python, it is a really useful language ;) My wife has a snake phobia, besides, I've just started learning Scala. > There is no sharing between processes, so > - both SD and GD are limited to the current session > (connection, postgres > process), no shared memory is involved > - GD is global between all python functions (global) > - SD is specific to each python function (static) Thanks. > The big gotcha is that these are all non-transactional > : if you rollback, > GD and SD stay the same, and when you issue a query, you can > assume the > state of SD and GD is random (due to previous queries) unless you > initialize them to a known value. Using txid_current() as a key should alleviate that. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
PFC wrote:
> Let's try this quick & dirty implementation of a local
> count-delta cache
> using a local in-memory hashtable (ie. {}).
> CREATE OR REPLACE FUNCTION update_count( key TEXT, delta INTEGER )
>RETURNS INTEGER
> AS $$
> if key in GD:
> GD[key] += delta
> else:
> GD[key] = delta
> return GD[key]
> $$ LANGUAGE plpythonu;
Thanks for the code, this seems to be very much what I was looking for.
I don't know plpythonu (nor python), just read a few docs now:
"The global dictionary SD is available to store data between function calls.
This variable is private static data. The global dictionary GD is public data,
available to all Python functions within a session. Use with care."
Does session == transaction or connection?
I don't understand the difference between SD and GD, private and public. Where
are the context boundaries?
Regards,
Stephen Denne.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer
focus, and courage. This email with any attachments is confidential and may be
subject to legal privilege. If it is not intended for you please advise by
reply immediately, destroy it and do not copy, disclose or use it in any way.
__
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Tom Lane wrote > Transaction commit is an exceedingly subtle and carefully structured > thing. Throwing random user-defined code into it ain't gonna happen. Deferred constraint triggers currently run random user-defined code. This'll do me. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Tom Lane wrote > "Stephen Denne" <[EMAIL PROTECTED]> writes: > > From: Tom Lane [mailto:[EMAIL PROTECTED] > >> As for 2) and 3), can't you look into the pg_settings view? > > > pg_settings view doesn't contain custom variables created > on the fly, > > Really? [ pokes around ... ] Hm, you're right, because > add_placeholder_variable() sets the GUC_NO_SHOW_ALL flag, and in this > usage it'll never be cleared. I wonder if we should change that. > > The whole thing is a bit of an abuse of what the mechanism > was intended > for, and so I'm not sure we should rejigger GUC's behavior to make it > more pleasant, but on the other hand if we're not ready to provide a > better substitute ... In my experiments with materialized views, I identified these problems as "minor" difficulties. Resolving them would allow further abuse ;) Aside: It is currently more cumbersome to get a function to run, if needed, at commit. Ideal solution would be something like "EXECUTE ON COMMIT my_function()" or maybe "SAVEPOINT my_name ON COMMIT my_function()", but these suggestions are made without investigating what provision the SQL standard has made to address this need. My use of mv.initialized means I can create variables when initializing a transaction, and afterwards know that they have values, but what I can't easily do is use those variables to identify which grouping keys have been updated. To do that I select & conditionally insert to a table for that explicit purpose. If select doesn't find the key, then I create variables named after that key, with zero values. Performance and efficiency-wise which would be better way of keeping track of grouping keys used in a transaction?: 1) Create a temp table, on commit drop, for the transaction, storing grouping keys affected. 2) Use a persistent table, storing txid and grouping keys affected, deleting txid rows at commit. 3) Use pg_settings, storing tx local grouping keys affected, existence check via catching an exception, listing via checking existence for all possible values (a possibility in my scenario). Speed is my priority, low disk IO is a probable means to that end, which is why I investigated using variables. Basically, (3) isn't a viable option, so what are the trade-offs between creating a temporary table per transaction, or using rows in a permanent table with a txid column? Here are some more plpgsql code fragments: mv := 'mv.' || view_name || '.' || key_value || '.'; When recording a grouping key as being affected by the transaction, create the variables with zeroes: PERFORM set_config(mv||'documents', '0', true); PERFORM set_config(mv||'last_addition', 'null', true); In an insert trigger: PERFORM set_config(mv||'documents', (current_setting(mv||'documents')::bigint + 1)::text, true); PERFORM set_config(mv||'last_addition', now()::text, true); In the defferred till commit trigger: UPDATE materialized_view set documents=documents+current_setting(mv||'documents')::bigint, last_addition=greatest(last_addition,nullif(current_setting(mv||'last_addition'),'null')::timestamp) where group_id = key_values.key_value; Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
(apologies for the email format & top posting, I've had to temporarily switch to using a web interface from home that doesn't seem to know what plain text is) pg_settings view doesn't contain custom variables created on the fly, (nor, from memory, ones defined in postgresql.conf. I'm not able to check and confirm that at the moment). Fixing that would satisfy 2 & 3 nicely. The docs on them say the ones in postgresql.conf are to be used by modules when they initialize, as the values to use when setting up actual real server variables, (able to store more types than just text), which I presume would appear in pg_settings. Updating existing variables is much quicker, over 10 updates per second. Regards, Stephen Denne. From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wed 16/04/2008 1:55 p.m. "Stephen Denne" <[EMAIL PROTECTED]> writes: > The custom variable class can also be used to aggregate the deltas within a > transaction, though there are some minor difficulties: > 1) They only store text > 2) You can only find out that a value has not been set by catching an > exception > 3) There is no way to list the settings. As for 2) and 3), can't you look into the pg_settings view? > The time to create them for the first time for a connection seems to vary a bit, between 200 and 1000 per minute. Yeah, that path is not optimized at all because it was never considered performance-critical. Updating an existing variable should be cheaper. regards, tom lane Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __
Re: [HACKERS] count(*) performance improvement ideas
(There is a possible performance bug mentioned at the end of the email, the
rest is further discussion regarding materialised views)
I wrote
> Pavan Deolasee wrote
> > On Thu, Mar 20, 2008 at 3:24 AM, Stephen Denne
> > <[EMAIL PROTECTED]> wrote:
> >
> > >
> > > Pavan also refers to deferred triggers, which has got me
> > thinking about another possible solution:
> > >
> > > Instead of inserting a delta row, that will be updated a
> > lot of times, create an on commit drop temp table named after
> > the txid and the grouping value (which is an integer in my
> > case, perhaps hash it if you're grouping by something that
> > doesn't easily convert to part of a table name),
> > > create an after insert initially deferred constraint to
> > call a function which will move the 'at commit' values of the
> > rows in the temp table to the summary table.
> > > The temp table will only have one row inserted, updated
> > many times, then on commit the trigger is fired once, and the
> > temp table is dropped.
> > >
> > > Does anyone think this will or won't work for some reason?
> >
> >
> > I think this should work, although you may need to take some
> > extra steps
> > to manage the summary table. Also, I think a single temp
> > table per transaction
> > should suffice. The temp table would have one row per "group
> > by" or "where"
> > condition on which you want to track the count. The
> > corresponding row will
> > be updated as and when the corresponding count changes. You
> would need
> > INSERT/DELETE/UPDATE triggers to do that. If there are any
> > subtransaction
> > aborts, that will be taken care by MVCC.
>
> Thanks for that. I had gone ahead and tried out the idea, and
> it was working 'ok'.
>
> Using one table per transaction has the benefit of less temp
> tables (but the same number of triggers waiting to run). It
> also removes the grouping key from the table name.
>
> I was using a single table per grouping key, with a single
> updated row in it.
> The benefit was simpler queries, and I could create an ON
> INSERT trigger that would be triggered only once when the
> temp table was created, and a 'zero' row was inserted,
> thereby separating the setup of the trigger from the
> maintenance of the delta.
One temp table per grouping key would presumably allocate at least one disk
page per grouping key.
This might result in pretty poor performance. Though if the number of updates
per grouping key is large, HOT would have plenty of room on the page to write
new row versions.
Does creation & dropping of temp tables result in system catalog bloat?
> I took a wild guess at a way of finding out whether the temp
> table already exists:
> not exists(select tablename from pg_catalog.pg_tables
> where tablename=temp_delta_txid_group)
> Is there a better/safer way?
Answering my own question:
There is a better way to do what I was doing, (not sure about a better way to
check existence of a temp table though)...
A custom variable class can be set up and used to record whether the
transaction in question has been set up. (Thanks to Andreas Kretschmer for
pointing those out in another thread on -general)
Alter this setting within postgresql.conf to add 'mv':
custom_variable_classes = 'mv'
Add this setting to postgresql.conf:
mv.initialized = 'false'
Then only set it to true local to the transaction.
Usage within a trigger:
IF NOT (current_setting('mv.initialized')::boolean) THEN
-- trigger a deferred constraint function:
INSERT INTO mv_txid_doc_type_summary VALUES (txid_current());
PERFORM set_config('mv.initialized', 'true', true);
END IF;
The custom variable class can also be used to aggregate the deltas within a
transaction, though there are some minor difficulties:
1) They only store text
2) You can only find out that a value has not been set by catching an exception
3) There is no way to list the settings.
The main benefit is that changing a variable's setting does not write a new row
version.
Creating new transactionally scoped variables seems to take around 150 to 200
bytes of the process ram per variable, (depending on the size of the value
stored).
The time to create them for the first time for a connection seems to vary a
bit, between 200 and 1000 per minute. No IO is being performed, CPU is at 100%
These statistics gathered when trying variations of this query:
select count(*) from (select set_config('mv.test11.' || s1.s1, s2.s2::text,
true) from (select s1 from generate_series(
Re: [HACKERS] Allow COPY from STDIN to absorb all input before throwing an error
Tom Lane wrote > Decibel! <[EMAIL PROTECTED]> writes: > > When restoring from pg_dump(all), if a problem occurs in a COPY > > command you're going to get a whole slew of errors, because > as soon > > as COPY detects a problem it will throw an error and psql will > > immediately switch to trying to process the remaining data > that was > > meant for COPY as if it was psql commands. This is confusing and > > annoying at best; it could conceivably trash data at worst > (picture > > dumping a table that had SQL commands in it). > > This is nonsense; it hasn't worked that way since we went to v3 > protocol. > > What is true is that if the COPY command itself is thoroughly borked, > the backend never tells psql to switch into COPY mode in the first > place. I had an annoying experience with COPY within psql yesterday. I had a dump of just three tables, which I wanted to investigate. I tried loading them into an empty database, using psql's \i command. The table creation failed as dependent tables/sequences where absent. The copy command failed as the tables did not exist. The data intended as the input to the copy statement resulted in a large number of error messages. > > My idea to avoid this situation is to add an option to COPY that > > tells it not to throw an error until it runs out of input data. > > This will not solve the problem, since again it only works if the COPY > command gets to execution. It is only now that I've found the \set ON_ERROR_STOP command, which I presume would have solved my problem. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Pavan Deolasee wrote
> On Thu, Mar 20, 2008 at 3:24 AM, Stephen Denne
> <[EMAIL PROTECTED]> wrote:
>
> >
> > Pavan also refers to deferred triggers, which has got me
> thinking about another possible solution:
> >
> > Instead of inserting a delta row, that will be updated a
> lot of times, create an on commit drop temp table named after
> the txid and the grouping value (which is an integer in my
> case, perhaps hash it if you're grouping by something that
> doesn't easily convert to part of a table name),
> > create an after insert initially deferred constraint to
> call a function which will move the 'at commit' values of the
> rows in the temp table to the summary table.
> > The temp table will only have one row inserted, updated
> many times, then on commit the trigger is fired once, and the
> temp table is dropped.
> >
> > Does anyone think this will or won't work for some reason?
>
>
> I think this should work, although you may need to take some
> extra steps
> to manage the summary table. Also, I think a single temp
> table per transaction
> should suffice. The temp table would have one row per "group
> by" or "where"
> condition on which you want to track the count. The
> corresponding row will
> be updated as and when the corresponding count changes. You would need
> INSERT/DELETE/UPDATE triggers to do that. If there are any
> subtransaction
> aborts, that will be taken care by MVCC.
Thanks for that. I had gone ahead and tried out the idea, and it was working
'ok'.
Using one table per transaction has the benefit of less temp tables (but the
same number of triggers waiting to run). It also removes the grouping key from
the table name.
I was using a single table per grouping key, with a single updated row in it.
The benefit was simpler queries, and I could create an ON INSERT trigger that
would be triggered only once when the temp table was created, and a 'zero' row
was inserted, thereby separating the setup of the trigger from the maintenance
of the delta.
I haven't explored the transactional implications of updating vs inserting
delta rows in the summary table at the time of transaction commit. The code
below updates the summary table, which I think could lead to a large delay or
deadlocks if there are other styles of updates on that table (other than
on-commit triggers)?
I also hadn't considered sub-transactions.
Below is a cut-down version of what I tried out.
I was summarizing more than just the number of documents shown below, I was
storing a sum, and two maximums of timestamps (using the 'greatest' function
for aggregating each record). These were extra fields in both the summary table
and the temp tables.
This is able to be made more generic by changing get_temp_table_name() to take
an additional couple of parameters specifying the name of the function to run
at commit, and an identifying key (eg 'doc_count' vs 'doc_size_sum'), or
perhaps including the delta value too, (which looks like it would simplify the
triggers on the tables whose changes we wish to summarize, except that it
doesn't cater for storing greatest or least aggregates.)
I took a wild guess at a way of finding out whether the temp table already
exists:
not exists(select tablename from pg_catalog.pg_tables where
tablename=temp_delta_txid_group)
Is there a better/safer way?
Here's part of the code I've got at the moment (edited here to cut it down to
the core example, so it may contain errors):
CREATE TABLE doc_type_summary (
document_type_id integer NOT NULL,
documents bigint NOT NULL DEFAULT 0,
CONSTRAINT pk_doc_type_summary PRIMARY KEY (document_type_id)
);
CREATE OR REPLACE FUNCTION process_delta() RETURNS TRIGGER AS
$$
BEGIN
EXECUTE 'UPDATE doc_type_summary set
documents=doc_type_summary.documents+d.documents from ' || TG_ARGV[0] || ' as d
where document_type_id = ' || TG_ARGV[1];
RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_temp_table_name(document_type_id integer)
RETURNS text AS
$$
DECLARE
temp_delta_txid_group text;
BEGIN
temp_delta_txid_group := 'temp_delta_' || txid_current() || '_' ||
document_type_id;
IF not exists(select tablename from pg_catalog.pg_tables where
tablename=temp_delta_txid_group) THEN
EXECUTE 'CREATE TEMP TABLE ' || temp_delta_txid_group || '(documents
bigint NOT NULL DEFAULT 0) ON COMMIT DROP';
EXECUTE 'CREATE CONSTRAINT TRIGGER ' || temp_delta_txid_group || '_trig
AFTER INSERT ON ' || temp_delta_txid_group || ' DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE process_delta ("'
Re: [HACKERS] count(*) performance improvement ideas
Mark Mielke wrote > This returns to the question of whether count of the whole table is useful, > or whether > count of a GROUP BY or WHERE is useful. > If GROUP BY or WHERE is useful, then trigger on UPDATE becomes necessary. True... for the example I gave I should have had an update trigger on my table. I neglected it as I based the example of a more complex multi-table example from my application, where I know that the value I'm grouping by doesn't change. > What is the direction here? Is it count of the whole table only? (<-- not > interesting to me) > Or count of more practical real life examples, which I completely agree with > Greg, > that this gets into the materialized view realm, and becomes very interesting. > In my current db project, I never count all of the rows in a table. However, > I do use count(*) with GROUP BY and WHERE. I'm trying to figure out how close I can come to a useful efficient materialized view with current production builds of postgresql, and identifying areas where changes to postgresql could make it easier. Currently I can see three areas of concern: 1) Turning it on My query to initially populate the materialized view table takes 6 hours on my (limited hardware) development system, which highlights the problem of when do you turn on the triggers. An outage is one way to guarantee that there are neither missing details nor double counted details. Would turning on the triggers and then running my initial population query in the same transaction work? 2) Table bloat I'm trying to group together a large number of +1 or -1 deltas into a single delta per transaction. This creates as many dead rows as there are updates. This is also a problem with Pavan's suggestion of maintaining a counter table. They can all be HOT updates in 8.3, but they still all create new row versions. Tom says "Collapsing a transaction's changes into a single row would require keeping some intra-transaction state, which is do-able at the C-code level but not from plpgsql." So can I hope that this problem is solvable as a contrib module that will work with at least 8.3? I'd still want to write plpgsql trigger function myself, as I know the aggregation rules, and call contributed functions to integrate with the collapsing of the transaction's changes into a single row. (Expect I'd need a small number of rows per transaction, as I need to create delta rows for each value of my grouping field involved in the transaction. 3) How to incorporate the deltas. With my technique, if the transaction rolls back the delta record becomes a dead row, if it commits, the delta is then visible to transaction started after this time. I need to have a regular process run to sum and remove the deltas, rewriting the summary rows. I'd like to be able to write an after-commit trigger that fires after changes in dependent tables that I identify, which can add the transaction's deltas to the summary table. I would want it to effectively be a new, small transaction. I think that if these three areas are addressed, then before even considering writing code to automatically convert any given view into a materialized view. 2) and 3) could perhaps be implemented with a per transaction map from my_custom_key to a prepared statement and a list of parameter values. Provide access to the values, making them updateable within the transaction. Have the statement automatically executed on commit. Pavan also refers to deferred triggers, which has got me thinking about another possible solution: Instead of inserting a delta row, that will be updated a lot of times, create an on commit drop temp table named after the txid and the grouping value (which is an integer in my case, perhaps hash it if you're grouping by something that doesn't easily convert to part of a table name), create an after insert initially deferred constraint to call a function which will move the 'at commit' values of the rows in the temp table to the summary table. The temp table will only have one row inserted, updated many times, then on commit the trigger is fired once, and the temp table is dropped. Does anyone think this will or won't work for some reason? Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. ___
Re: [HACKERS] count(*) performance improvement ideas
Tom Lane wrote > "Stephen Denne" <[EMAIL PROTECTED]> writes: > > What I was asking about with those questions, is if a > > single row is inserted in transaction A, and updated 1000 > > times still within transaction A, then transaction A > > commits... does a single row version get written, or 1001, > > 1000 of which are removable? > > Umm ... AFAICS there is no need for an UPDATE to touch the count table > at all. You'd only need ON INSERT and ON DELETE triggers. I'm not referring to updates of my base table... the single row inserted was referring to the delta row... I'm trying to group together a large number of +1 or -1 deltas into a single delta per transaction. A cut down example: CREATE TABLE document_type_summary_detail ( document_type_id integer NOT NULL, txid bigint NOT NULL DEFAULT 0, documents bigint NOT NULL DEFAULT 0, CONSTRAINT pk_document_type_summary PRIMARY KEY (document_type_id, txid) ); CREATE OR REPLACE FUNCTION document_count_trig() RETURNS TRIGGER AS $$ DECLARE BEGIN IF TG_OP = 'INSERT' THEN UPDATE document_type_summary_detail set documents=documents+1 where document_type_id = NEW.document_type_id and txid=txid_current(); IF NOT FOUND THEN INSERT INTO document_type_summary_detail (document_type_id,documents,txid) VALUES(NEW.document_type_id,1,txid_current()); END IF; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN UPDATE document_type_summary_detail set documents=documents-1 where document_type_id = OLD.document_type_id and txid=txid_current(); IF NOT FOUND THEN INSERT INTO document_type_summary_detail (document_type_id,documents,txid) VALUES(OLD.document_type_id,-1,txid_current()); END IF; RETURN OLD; END IF; END; $$ LANGUAGE 'plpgsql'; create trigger document_count_trig before insert or delete on document for each row execute procedure document_count_trig(); --one off setup: insert into document_type_summary_detail (document_type_id,txid,documents) select dt.id, 0, count(d.id) from document_type dt left join document d on d.document_type_id = dt.id group by 1,2; --useful view: CREATE OR REPLACE VIEW document_type_summary AS SELECT document_type_id, sum(documents) AS documents FROM document_type_summary_detail GROUP BY document_type_id; --scheduled cleanup: CREATE OR REPLACE FUNCTION document_type_summary_aggregate() RETURNS void AS $$ BEGIN INSERT INTO document_type_summary_detail(document_type_id) select distinct document_type_id from document_type_summary_detail except select document_type_id from document_type_summary_detail where txid=0; UPDATE document_type_summary_detail set documents=v.documents from document_type_summary as v where document_type_summary_detail.document_type_id = v.document_type_id and document_type_summary_detail.txid=0 and exists (select 1 from document_type_summary_detail ss where ss.document_type_id = document_type_summary_detail.document_type_id and ss.txid <> 0); DELETE FROM document_type_summary_detail where txid <> 0; END; $$ LANGUAGE 'plpgsql'; My assumption is that this solves the "locking causes serialisation of transactions" problem as the only rows updated are those inserted by the same transaction. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
(Sorry for the repeat email Tom, I forgot the cc's) Tom Lane wrote: > "Stephen Denne" <[EMAIL PROTECTED]> writes: > > I initially thought that what you meant by "having > > transactions enter "delta" entries" was that I have a trigger > > that would create a row each time it was called, writing how > > many records where inserted or deleted. I didn't understand > > how this would be much of an improvement, as each of my rows > > would contain either +1 or -1. > > Well, ideally you'd aggregate all the deltas caused by a particular > transaction into one entry in the counting table. Yes, that's what I was attempting to do, but without changing the application code. Using txid_current() can do that, so each of a large number of individual inserts or deletes within a transaction updates the same delta row for that transaction. I haven't found any references to this being a solution, and thought it was worth recording. > > However I'm not after a fast count(*) from table, but more > like a fast > > select grouping_id, count(*) from my_table group by grouping_id > > You could apply the same technique across each group id, though this > certainly is getting beyond what any built-in feature might offer. Agreed. I've tried it out now, and am fairly happy with what I've got. > > Can you clarify the lack of MVCC problems? > > The point there is that the "right answer" actually depends on the > observer, since each observer might have a different snapshot and > therefore be able to "see" a different set of committed rows in the > underlying table. The multiple-delta table handles this > automatically, > because you can "see" a delta entry if and only if you could "see" > the underlying-table changes it represents. > > > Does this idea apply with the same efficiency in pre 8.3, > non-HOT implementations? > > I didn't claim it was amazingly efficient in any implementation ;-). > HOT in particular is nearly useless since most rows in the count > table will never be updated, only inserted and eventually deleted. > You might get some mileage on the base row, but that'd be about it. > The count table will need frequent vacuums as well as frequent > aggregation scans. > > It should beat scanning a large underlying table, but it's hardly > gonna be free. What I was asking about with those questions, is if a single row is inserted in transaction A, and updated 1000 times still within transaction A, then transaction A commits... does a single row version get written, or 1001, 1000 of which are removable? I tested this with a small summary table freshly vacuum full'ed. 10 transactions, one after the other, each transaction inserted three delta rows, and updates one of them three times, and the other two five times each. So 3 inserts and 13 updates per transaction. The updates only affect non-indexed fields in rows created in the same transaction. The next vacuum full found 52 removable row versions. I repeated the test, and got 13 removable row versions. I repeated the test again, and got 13 removable row versions. I repeated just one of the ten transactions, 13 removable row versions. All inserts and updates are probably in the one page that has a fair bit of free space. Is it possible to update the HOT code to re-use row versions on the same page if they were created in the same transaction? Conclusion: making use of txid_current(), I can get single delta rows with deltas of 1, but doing so creates 1 dead row versions. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Tom Lane wrote > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > > I am sure this must have been discussed before. > > Indeed. Apparently you didn't find the threads in which the idea of > having transactions enter "delta" entries was discussed? Solves both > the locking and the MVCC problems, at the cost that you need to make > cleanup passes over the counter table fairly often. I can't find any posts that directly address what I was looking for. In my situation I have a small number of concurrent transactions with each transaction running a large number of single row inserts or deletes. However I'm not after a fast count(*) from table, but more like a fast select grouping_id, count(*) from my_table group by grouping_id I initially thought that what you meant by "having transactions enter "delta" entries" was that I have a trigger that would create a row each time it was called, writing how many records where inserted or deleted. I didn't understand how this would be much of an improvement, as each of my rows would contain either +1 or -1. But I just realised you might mean to include a txid row in my table of deltas, and in my trigger insert or update that row where txid = txid_current() (per grouping_id) Is that what is recommended? No locking problems as each transaction is only updating its own rows. Can you clarify the lack of MVCC problems? Do new versions of rows get created if the original version of the row was created in the current transaction? Does this idea apply with the same efficiency in pre 8.3, non-HOT implementations? Any advice on creating or avoiding indexes on the tables in question? I can think of two options for a performing the cleanup passes using current functionality: 1) Using Listen/Notify Issue a notify whenever a new row is inserted for a transaction. They get delivered post transaction commit don't they? And not at all on rollback? Have an application listening for them, performing the aggregation & cleanup work. 2) Use a schedule, based on local knowledge of expected number of transactions over time. So I'd rephrase Pavan's suggestion as a request to have post-transaction commit triggers that have access to (at least) the txid of the transaction that was committed. Suggested syntax is to add the option "TRANSACTION" (or perhaps "COMMIT") to the CREATE TRIGGER statement: CREATE TRIGGER name AFTER INSERT OR UPDATE OR DELETE ON table FOR EACH TRANSACTION EXECUTE PROCEDURE funcname ( arguments ); Any of the triggering actions on the specified table ensure that the function is called once if the transaction commits. Requires a new TG_LEVEL. TG_OP could be the first action triggered. Would implementing this be extremely difficult due to transferring information from within the transaction to outside the transaction? If so, perhaps I'd get the same benefit from having a trigger set up to fire pre-commit (or pre-prepare), and be a part of the transaction. Would the locking difficulties be reduced as the lock would not be required till late in the game, and not held for long? Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Estimating geometric distributions
I wrote: > > I have a field whose distribution of frequencies of values is > > roughly geometric, rather than flat. > My problem is frequent > > over-estimation of rows when restricting by this field with > > values not known at plan time. > Is there any facility already in PostgreSQL to help me here? > > Hopefully an index type that I don't know about yet? > (Geometric distributions are similar to those found in word > count distributions). > > If not... is there any merit in this idea: > > During the analyze process, the geometric mean of sampled > rows was calculated, and if determined to be significantly > different from the arithmetic mean, stored in a new stats > column. When estimating the number of rows that will be > returned by queries of the form shown above, if there is a > geometric mean stored, use it instead of the arithmetic mean. I came up with another (much easier) means of adjusting the planners estimation of how many rows will be returned: Increase the number of distinct values in the statistics. For example: update pg_statistic set stadistinct=2691 where starelid=29323 and staattnum=2; I can then pick a number of distinct values such that the effective arithmetic mean is equal to what I calculated the geometric mean to be. Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Maximum statistics target
> We could remove the hard limit on statistics target and > impose the limit > instead on the actual size of the arrays. Ie, allow people to > specify larger > sample sizes and discard unreasonably large excess data > (possibly warning them > when that happens). > > That would remove the screw case the original poster had > where he needed to > scan a large portion of the table to see at least one of > every value even > though there were only 169 distinct values. > > -- > Gregory Stark That was my use case, but I wasn't the OP. Your suggestion would satisfy what I was trying to do. However, a higher stats target wouldn't solve my root problem (how the planner uses the gathered stats), and the statistics gathered at 1000 (and indeed at 200) are quite a good representation of what is in the table. I don't like the idea of changing one limit into two limits. Or are you suggesting changing the algorithm that determines how many, and which pages to analyze, perhaps so that it is adaptive to the results of the analysis as it progresses? That doesn't sound easy. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Estimating geometric distributions
I wrote:
> I have a field whose distribution of frequencies of values is
> roughly geometric, rather than flat.
> Total rows = 36 million
> relpages=504864
> Distinct field values in use = 169
> 10 values account for 50% of the rows.
> 41 values account for 90% of the rows.
>
> After setting statistics target to 1000 for that field, and
> analyzing the table, the statistics row for that field had 75
> most frequent values and a histogram with 77 entries in it.
> Estimating 152 values in total.
"public";"mytable";"myfield";0;4;152;"{202,179,8,181,173,207,6,118,107,205,182,4,54,247,168,77,169,53,120,159,149,174,167,156,148,150,56,108,66,119,5,99,96,175,97,208,1,130,10,102,228,101,121,50,11,152,32,12,78,221,55,244,241,252,203,116,103,184,154,153,238,65,49,220,83,98,111,85,139,242,240,260,7,109,114}";"{0.0836433,0.0781667,0.0738367,0.0598533,0.04629,0.04447,0.0359833,0.0314267,0.0278333,0.0268,0.0251433,0.0244867,0.02438,0.0223433,0.0207567,0.0189667,0.0168833,0.01582,0.0150267,0.0141767,0.0130467,0.0128933,0.0125767,0.0123567,0.0116567,0.0114967,0.01048,0.01037,0.00994667,0.00987667,0.00977667,0.00965333,0.00916333,0.00828667,0.00732667,0.00712,0.00629,0.00624,0.00576667,0.00558667,0.00477667,0.00475333,0.00410333,0.00405667,0.00371667,0.00334667,0.00334,0.00312667,0.00312667,0.00302,0.00300333,0.00295,0.00287333,0.00271,0.00267,0.00240667,0.00224,0.00221333,0.00215333,0.0021,0.00205667,0.00202667,0.00197333,0.00197333,0.00168667,0.00166,0.00159333,0.00159,0.00154667,0.00150333,0.00149,0.0013,0.00132,0.00112667,0.00104}";"{2,9,9,9,67,76,84,84,86,87,87,88,95,100,100,100,104,105,105,110,112,112,128,137,137,138,143,144,144,144,151,155,155,155,157,157,158,171,171,183,185,185,185,185,187,194,199,199,200,200,201,204,204,209,209,214,214,214,214,215,217,225,225,225,229,239,239,249,250,250,253,253,255,257,261,262,266}";0.449246
My problem is frequent
> over-estimation of rows when restricting by this field with
> values not known at plan time.
examples:
select * from mytable where myfield = ?;
select * from mytable where myfield in (subquery);
My arithmetic mean of the frequencies is 214200
My geometric mean is 13444
However analyze didn't find all my values, and thinks that there are only 152
of them, so it uses a mean of 238046
When the subquery is estimated to return three myfield values, the query
estimates 714138 rows, and chooses a sequential scan over mytable (myfield is
indexed).
explain select * from mytable where myfield in (values (1),(2),(3));
Hash IN Join (cost=0.08..1009521.37 rows=714138 width=86)
Hash Cond: (mytable.myfield = "*VALUES*".column1)
-> Seq Scan on mytable (cost=0.00..866693.76 rows=36182976 width=86)
-> Hash (cost=0.04..0.04 rows=3 width=4)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=4)
I think this query is much more likely to return around 4 rows, and a
Bitmap Index Scan should be used.
explain select * from mytable where myfield in (values (1),(2));
Nested Loop (cost=4445.11..931383.93 rows=476092 width=86)
-> Unique (cost=0.04..0.04 rows=2 width=4)
-> Sort (cost=0.04..0.04 rows=2 width=4)
Sort Key: "*VALUES*".column1
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4)
-> Bitmap Heap Scan on mytable (cost=4445.08..462716.37 rows=238046
width=86)
Recheck Cond: (mytable.myfield = "*VALUES*".column1)
-> Bitmap Index Scan on myindex (cost=0.00..4385.56 rows=238046
width=0)
Index Cond: (mytable.myfield = "*VALUES*".column1)
The expected number of loops (2 here, 3 above) through the Bitmap Heap Scan *
462716.37 > 1009521.37, but the cost estimate is far too high in the general
case. It should be closer to 26000 per loop if adjusted for my expectation of
the number of rows, being 13444 per loop. As such, you should need to expect
close to 40 myfield values being returned by the subquery before choosing a
sequential scan.
Is there any facility already in PostgreSQL to help me here?
Hopefully an index type that I don't know about yet? (Geometric distributions
are similar to those found in word count distributions).
If not... is there any merit in this idea:
During the analyze process, the geometric mean of sampled rows was calculated,
and if determined to be significantly different from the arithmetic mean,
stored in a new stats column. When estimating the number of rows that will be
returned by queries of the form shown above, if there is a geometric mean
stored, use it instead of the arithmetic mean.
Regards,
Stephen Denne.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer
focus, and courage. This email with any attachments is confidential and may be
subject to legal privilege. I
Re: [HACKERS] Maximum statistics target
Tom Lane wrote: > Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > > On Fri, Mar 07, 2008 at 07:25:25PM +0100, Peter Eisentraut wrote: > >> What's the problem with setting it to ten million if I > have ten million values > >> in the table and I am prepared to spend the resources to > maintain those > >> statistics? > > > That it'll probably take 10 million seconds to calculate the plans > > using it? I think Tom pointed there are a few places that are O(n^2) > > the number entries... > > I'm not wedded to the number 1000 in particular --- obviously that's > just a round number. But it would be good to see some > performance tests > with larger settings before deciding that we don't need a limit. I recently encountered a situation where I would have liked to be able to try a larger limit (amongst other ideas for improving my situation): I have a field whose distribution of frequencies of values is roughly geometric, rather than flat. Total rows = 36 million relpages=504864 Distinct field values in use = 169 10 values account for 50% of the rows. 41 values account for 90% of the rows. After setting statistics target to 1000 for that field, and analyzing the table, the statistics row for that field had 75 most frequent values and a histogram with 76 entries in it. Estimating 151 values in total. For this situation using a larger statistics target should result in more pages being read, and a more accurate record of statistics. It shouldn't result in significantly more work for the planner. It wouldn't solve my problem though, which is frequent over-estimation of rows when restricting by this field with values not known at plan time. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ad Hoc Indexes
Justin wrote: > although in both case with and with out indexes the estimate still failed to > return the correct number by allot. The improvement wasn't to the part of the query that had the bad cost estimate, it was to the part that was being performed hundreds of times instead of the one time the planner estimated. The planner still thought it was only going to perform a sequential scan of your wooper table once. So even if there had been any Ad Hoc Index creation code that had been used to consider creating indexes as part of a plan cost estimate, it wouldn't have bothered creating any indexes on wooper. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Ad Hoc Indexes
Justin wrote: > Then why are the estimates so far off? Perhaps a really high correlation between those records where coitem_status = 'O' and those records in your date range. The planner estimates 1 row when restricting by both restrictions, whereas there where 458. The 1 row was probably a result of a small % for status='O' multiplied by a small % for the date range. > If estimates where correct would it improve the performance that much. Possibly, but a better performance gain might be obtained by rewriting the query, changing the case expression to something along the lines of: coalesce(( select wrkcnt_code || ' Operation Completed :' || cast(wooper_seqnumber as text) from wooper inner join wrkcnt on wrkcnt_id = wooper_wrkcnt_id where wooper_rncomplete = true and wooper_wo_id = coitem_order_id order by wooper_seqnumber desc limit 1 ),'No Operation Completed') as LastFinshedOp regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Locale vs LIKE
Given the improvements in 8.3 listed in the release notes: - Improve efficiency of LIKE/ILIKE, especially for multi-byte character sets like UTF-8 (Andrew, Itagaki Takahiro) Does this still hold: http://www.postgresql.org/docs/8.3/interactive/locale.html "The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them." i.e. Do I still have to either initdb --locale=C or explicitly use text_pattern_ops? (Queries include predicates of the form [indexed text expression] like "ABC%") Stephen Denne Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Possible future performance improvement: sort updates/deletes by ctid
> At the planner level that would be entirely the wrong way to go about > it, because that's forcing the equivalent of a nestloop join, which is > very unlikely to be faster for the numbers of rows that we're talking > about here. The reason it looks faster to you is that the benefits of > updating the document_file rows in ctid order outweigh the > costs of the > dumb join strategy ... but what we want to achieve here is to > have both > benefits, or at least to give the planner the opportunity to make a > cost-driven decision about what to do. Ok. Here are some more data points, using a smaller table, v8.2.6: Seq Scan on document_file df (cost=0.00..208480.85 rows=25101 width=662) (actual time=0.239..773.834 rows=25149 loops=1) SubPlan -> Index Scan using pk_document_id on document d (cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.015 rows=1 loops=25149) Index Cond: (id = $0) Total runtime: 4492.363 ms vs Hash Join (cost=1048.85..6539.32 rows=25149 width=666) (actual time=575.079..1408.363 rows=25149 loops=1) Hash Cond: (df.document_id = d.id) -> Seq Scan on document_file df (cost=0.00..4987.49 rows=25149 width=662) (actual time=60.724..824.195 rows=25149 loops=1) -> Hash (cost=734.49..734.49 rows=25149 width=8) (actual time=40.271..40.271 rows=25149 loops=1) -> Seq Scan on document d (cost=0.00..734.49 rows=25149 width=8) (actual time=0.055..22.559 rows=25149 loops=1) Total runtime: 34961.504 ms These are fairly repeatable for me after doing a vacuum full analyze of the two tables. Have I simply not tuned postgres so that it knows it has everything on a single old IDE drive, not split over a few sets of raided SSD drives, hence random_page_cost should perhaps be larger than 4.0? Would that make the second estimate larger than the first estimate? Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Possible future performance improvement: sort updates/deletes by ctid
> From: Tom Lane [mailto:[EMAIL PROTECTED] > doubtless-oversimplified It looks equivalent. > With that patch, I got the results ... >-> Hash Join (cost=123330.50..1207292.72 rows=408 > width=618) (actual time=20186.510..721120.455 rows=400 loops=1) The plan from here is equivalent to the query plan that I had. In an update query, does the actual time = 721120 mean that after 12 minutes it had completed figuring out what to update, and what to? > This is with default shared_buffers (32MB) and work_mem (1MB); I had tried a few larger settings, and though I had fewer temp files created, they still took longer than I was willing to wait to process. I did figure out that contention with the background writer or checkpoint processing probably wasn't a large contributor. How hard is it to match, recognise potential benefit, and rewrite the query from UPDATE ONLY document_file AS df SET document_type_id = d.document_type_id FROM document AS d WHERE d.id = document_id; to UPDATE ONLY document_file AS df SET document_type_id = (SELECT d.document_type_id FROM document AS d WHERE d.id = document_id); Which is several orders of magnitude faster for me. Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.3RC1 on windows missing descriptive Event handle names
I said... > On Windows XP, using Process Explorer with the lower pane > showing Handles, not all postgres.exe processes are including > an "Event" type with a description of what the process is doing. I've had difficulty reproducing this, but I now suspect that it is only happening when running both v8.2 and v8.3rc1 at once, and I think it is the second started that is missing the process descriptions. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(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: [HACKERS] [PATCHES] Proposed patch: synchronized_scanningGUCvariable
Jeff Davis wrote > > Well clusteredness is used or could be used for a few > different heuristics, > > not all of which this would be quite as well satisfied as > readahead. But for > > Can you give an example? Treating a file as a circular structure does > not impose any significant cost that I can see. (Pure speculation follows... if you prefer facts, skip this noise) The data used to create pg_stats.correlation is involved in estimating the cost of an index scan. It could also be used in estimating the cost of a sequential scan, if the query includes a limit. Consider: select * from huge_table_clustered_by_A where Ahttp://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 8.3RC1 on windows missing descriptive Event handle names
On Windows XP, using Process Explorer with the lower pane showing Handles, not all postgres.exe processes are including an "Event" type with a description of what the process is doing. At the moment, I have ten postgres processes shown as being at the bottom of the tree, three of which do not have such descriptions. The processes that do show an Event type handle show these names: \BaseNamedObjects\pgident: postgres: wal writer process \BaseNamedObjects\pgident: postgres: autovacuum launcher process plus one per connection like: \BaseNamedObjects\pgident: postgres: postgres mydatabase 127.0.0.1(1954) idle Comparing the list of processes to those of 8.2, I suspect that the processes missing this detail are the log writer (one of the processes does have a File Handle on the log file), and the stats collector. I have autovacuum enabled for 8.3rc1, and disabled for 8.2. PostgreSQL is started as a service. Stephen Denne Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ ---(end of broadcast)--- TIP 6: explain analyze is your friend
