[PATCHES] Patch for testing query modes on pgbench
Here is a updated patch to add query modes into pgbench in order to measure performance of simple protocol, extended protocol and prepared statements with it. [PATCHES] Patch for testing query modes on pgbench http://momjian.us/mhonarc/patches/msg00335.html A new runtime option (-M) is added: -M querymode Choose the query mode from the follows. default is simple. - simple: using simple query protocol. - extended: using extended protocol. - prepared: using extended protocol with prepared statements. Extended protocol is slower than simple protocol. I think it is a problem because we have to use the protocol (and cannot use prepared statements) in some situations, such as connectiong with JDBC to partitioned tables. $ pgbench -S -n -c5 -t1 -M simple tps = 3567.351598 (excluding connections establishing) $ pgbench -S -n -c5 -t1 -M extended tps = 2877.697842 (excluding connections establishing) $ pgbench -S -n -c5 -t1 -M prepared tps = 4532.680627 (excluding connections establishing) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgbench_querymode.patch Description: Binary data -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] UPDATE using sub selects
Hi Tom, ... eg backend/nodes/, optimizer/util/clauses.c, ruleutils.c...) Actually, on further thought ruleutils.c represents one of the biggest stumbling blocks here. We have to be able to reverse-compile the parse tree into something that's at least semantically equivalent to the original query. The existing kluge for UPDATE SET (columns) = ... can ignore this because it rearranges the query into a sematically equivalent update with independent SET targets, but the whole problem with sub-select updates is that there *is* no semantically equivalent command with a flat targetlist. So one way or another there will have to be more information in the parse tree than there is now. If we use Params that can be traced back to specific SubLink list entries, it might be okay to finesse this by having ruleutils.c check for successive targetlist entries that reference outputs of the same SubLink. That's pretty ugly but it might be better than changing the representation of targetlists, which is something that's understood by one heck of a lot of code. Thanks a lot for taking a look at this patch. As mentioned and detailed out by you, this patch *does* need a lot more amount of work and is certainly not a simpler effort that I had envisioned it to be earlier. Another issue with the patch as it stands today is that it does not work with correlated subqueries. This will require some more work too. So for example, something of this sort does not work yet: UPDATE update_tbl set (a, b) = (select a, b from other_tbl where c = update_tbl.c) where a = 10; I will try to have another shot at it if I can, before the next commit fest. Thanks and Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] UPDATE using sub selects
Patch removed from patch queue --- NikhilS, please resubmit when you are ready. Thanks. --- NikhilS wrote: Hi, As per discussion on -hackers, a patch which allows updates to use subselects is attached with this mail. As per discussion with Tom, I have adopted the following approach: * Introduce ROWEXPR_SUBLINK type for subqueries that allows multiple column outputs. * Populate the targetList with PARAM_SUBLINK entries dependent on the subselects. * Modify the targets in-place into PARAM_EXEC entries in the make_subplan phase. The above does not require any kluges in the targetList processing code path at all. UPDATEs seem to work fine using subselects with this patch. I have modified the update.sql regression test to include possible variations . No documentation changes are present in this patch. Feedback, comments appreciated. Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] Moving snapshot code around
Hi, I'm playing with the snapshot code to create a new module to stash used snapshots and refcount them. It occured to me that a first easy step is to separate the relevant code from tqual.c into a new file, snapshot.c, and split tqual.h in two creating snapshot.h. Basically the internals of snapshots are now in tqual.c/h, and the external interface is snapshot.c/h. The nice thing about it is that most users of snapshots only need the external interface, so most details can remain behind tqual.h which is now a seldom-included header. (The bad news is that the widely used heapam.h still has to include it, because it needs the HTSU_Result enum, so tqual.h is still indirectly included in a lot of places. I think I can easily move the enum definition to snapshot.h but it seems weird.) So here's a patch to do this. It just moves code around -- there's no extra functionality here. The other approach, of course, is to just keep all the code in tqual.c and not create a separate module at all. Opinions? I prefer to keep them separate, but I'm not wedded to it if there's any strong reason not to do it. Also, the line is currently blurred because some users of snapshots mess with the internals directly (setting snapshot-curcid), but we could clean that up and make it so that those become external interface users too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. *** 00head/src/backend/access/transam/subtrans.c 2008-01-09 13:04:32.0 -0300 --- 01snapshot/src/backend/access/transam/subtrans.c 2008-03-18 12:13:18.0 -0300 *** *** 31,37 #include access/slru.h #include access/subtrans.h #include access/transam.h ! #include utils/tqual.h /* --- 31,37 #include access/slru.h #include access/subtrans.h #include access/transam.h ! #include utils/snapshot.h /* *** 00head/src/backend/access/transam/transam.c 2008-03-14 09:54:13.0 -0300 --- 01snapshot/src/backend/access/transam/transam.c 2008-03-18 12:04:01.0 -0300 *** *** 22,28 #include access/clog.h #include access/subtrans.h #include access/transam.h ! #include utils/tqual.h /* --- 22,28 #include access/clog.h #include access/subtrans.h #include access/transam.h ! #include utils/snapshot.h /* *** 00head/src/backend/catalog/catalog.c 2008-02-26 16:42:19.0 -0300 --- 01snapshot/src/backend/catalog/catalog.c 2008-03-18 15:06:02.0 -0300 *** *** 38,43 --- 38,44 #include storage/fd.h #include utils/fmgroids.h #include utils/relcache.h + #include utils/tqual.h #define OIDCHARS 10 /* max chars printed by %u */ *** 00head/src/backend/commands/explain.c 2008-01-09 13:04:32.0 -0300 --- 01snapshot/src/backend/commands/explain.c 2008-03-18 14:59:28.0 -0300 *** *** 31,36 --- 31,37 #include utils/guc.h #include utils/lsyscache.h #include utils/tuplesort.h + #include utils/tqual.h /* Hook for plugins to get control in ExplainOneQuery() */ *** 00head/src/backend/commands/variable.c 2008-01-09 13:04:33.0 -0300 --- 01snapshot/src/backend/commands/variable.c 2008-03-18 12:05:09.0 -0300 *** *** 25,31 #include utils/acl.h #include utils/builtins.h #include utils/syscache.h ! #include utils/tqual.h #include mb/pg_wchar.h /* --- 25,31 #include utils/acl.h #include utils/builtins.h #include utils/syscache.h ! #include utils/snapshot.h #include mb/pg_wchar.h /* *** 00head/src/backend/utils/adt/txid.c 2008-01-09 13:04:40.0 -0300 --- 01snapshot/src/backend/utils/adt/txid.c 2008-03-18 14:58:34.0 -0300 *** *** 26,31 --- 26,32 #include funcapi.h #include libpq/pqformat.h #include utils/builtins.h + #include utils/tqual.h #ifndef INT64_IS_BUSTED *** 00head/src/backend/utils/time/Makefile 2008-02-20 00:53:22.0 -0300 --- 01snapshot/src/backend/utils/time/Makefile 2008-03-18 11:36:21.0 -0300 *** *** 12,17 top_builddir = ../../../.. include $(top_builddir)/src/Makefile.global ! OBJS = combocid.o tqual.o include $(top_srcdir)/src/backend/common.mk --- 12,17 top_builddir = ../../../.. include $(top_builddir)/src/Makefile.global ! OBJS = combocid.o tqual.o snapshot.o include $(top_srcdir)/src/backend/common.mk *** 00head/src/backend/utils/time/snapshot.c 1969-12-31 21:00:00.0 -0300 --- 01snapshot/src/backend/utils/time/snapshot.c 2008-03-18 14:38:24.0 -0300 *** *** 0 --- 1,176 + /*- + * snapshot.c + * PostgreSQL snapshot management code. + * + * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + *
Re: [PATCHES] Patch to inline stable SQL set returning UDFs
Richard Rowell [EMAIL PROTECTED] writes: I discussed this hack with Neil and Josh in Ottawa back in May. I took a stab at it a few weeks ago and below are the results. It is mostly modeled after the existing inline_function. The approach taken is to recursively cleanup the RTable where stable SQL functions are found in it. Applied with revisions --- the recursion issue wasn't being managed properly, and there were some other problems ... regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Wrong result with pgbench -C option?
Patch applied. Thanks. -- Tatsuo Ishii SRA OSS, Inc. Japan Hi, I ran pgbench with -C option. Here is an output. % pgbench -C -c 10 -t 100 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 10 number of transactions per client: 100 number of transactions actually processed: 1000/1000 tps = 83.209663 (including connections establishing) tps = 83.209663 (excluding connections establishing) The first tps was equal to the second tps. I think it is wrong because pgbench with -C option connects per transaction. The attached patch calculates total connection time. Here is an output with revised pgbench. % ./pgbench -C -c 10 -t 100 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 10 number of transactions per client: 100 number of transactions actually processed: 1000/1000 tps = 108.410991 (including connections establishing) tps = 228.657561 (excluding connections establishing) Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Patch for testing query modes on pgbench
Here is a updated patch to add query modes into pgbench in order to measure performance of simple protocol, extended protocol and prepared statements with it. [PATCHES] Patch for testing query modes on pgbench http://momjian.us/mhonarc/patches/msg00335.html A new runtime option (-M) is added: -M querymode Choose the query mode from the follows. default is simple. - simple: using simple query protocol. - extended: using extended protocol. - prepared: using extended protocol with prepared statements. Extended protocol is slower than simple protocol. I think it is a problem because we have to use the protocol (and cannot use prepared statements) in some situations, such as connectiong with JDBC to partitioned tables. $ pgbench -S -n -c5 -t1 -M simple tps = 3567.351598 (excluding connections establishing) $ pgbench -S -n -c5 -t1 -M extended tps = 2877.697842 (excluding connections establishing) $ pgbench -S -n -c5 -t1 -M prepared tps = 4532.680627 (excluding connections establishing) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center I got following: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -I../../src/interfaces/libpq -I. -I../../src/include -D_GNU_SOURCE -c -o pgbench.o pgbench.c pgbench.c: In function `parseQuery': pgbench.c:1105: error: `var' undeclared (first use in this function) pgbench.c:1105: error: (Each undeclared identifier is reported only once pgbench.c:1105: error: for each function it appears in.) pgbench.c:1092: warning: unused variable `hvar' (note that the line number might be different what it should be in your work file since I have applied Yoshiyuki's patches in prior). The cause of this is obvious and easy to fix. However I wonder this is because of accidental old or wrong patches. Can you resubmit new patches against CVS Head? -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Patch for testing query modes on pgbench
Tatsuo Ishii [EMAIL PROTECTED] wrote: The cause of this is obvious and easy to fix. However I wonder this is because of accidental old or wrong patches. Can you resubmit new patches against CVS Head? Thanks for your review. Sorry, it's a wrong patch. I found another bugs in handling external scripts and ':' at cast operators '::'. I'll send new one. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgbench_querymode-3.patch Description: Binary data -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Patch for testing query modes on pgbench
Patches applied. Thanks. -- Tatsuo Ishii SRA OSS, Inc. Japan Tatsuo Ishii [EMAIL PROTECTED] wrote: The cause of this is obvious and easy to fix. However I wonder this is because of accidental old or wrong patches. Can you resubmit new patches against CVS Head? Thanks for your review. Sorry, it's a wrong patch. I found another bugs in handling external scripts and ':' at cast operators '::'. I'll send new one. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
