[GENERAL] question about alternate ordering of results
Hi, in the old days (version 7.x) I created a set of btree operators create operator <& ( procedure = mytext_lt, leftarg = text, rightarg = text, commutator = >&, negator = >=&, restrict = scalarltsel, join = scalarltjoinsel ); etc. for a completeoperator class create operator class mytext_ops for type text using btree as operator 1 <& (text,text), ); Some tables have an index using this operator class create index blah_t on blah using btree (col1 mytext_ops); I also created a few extra operators create operator <&- ( procedure = mytext_lt_x, leftarg = text, rightarg = text, commutator = >&-, negator = >=&-, restrict = scalarltsel, join = scalarltjoinsel ); and could use them in a query, like select from blah order by col1; select from blah order by col1 using <&; select from blah order by col1 using <&-; (resulting in 3 different result orderings) BTW: the difference between the two ops is "natural" ordering of numerics: if two strings start to differ at "3rd" vs "17th", the <& returns them in alpha sort and the <&- produces proper numeric order Now, in versions 8 and later the "using <&-" is rejected, the ordering op "needs to be < or > member of a btree operator class". What is needed to create the old behaviour again - create a complete operator class, including new names for the unchanged equals/not equals function? - adding another index to get the feature Is this relevant to performance? I guess that an index using one opclass and ordering using the other one would result in an index scan to locate the data and then an extra ordering pass to order them the other way Regards Wolfgang Hamann -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 9.0.7 weird planner decision (rows in plan close to reality but plan suboptimal)
Hi, Today on one of databases under my management I found very strange plan for simple query. Postgresql 9.0.7 on Linux, random_page_cost=4 seq_page_cost=1 The query and plan: db=# EXPLAIN (ANALYZE, COSTS, BUFFERS) select obj_id, obj_commented,p2o_id FROM blog_post as obj JOIN person2obj ON p2o_obj_obj_id = obj_id JOIN person2obj_counters ON p2oc_id = p2o_id WHERE obj_status_did = 1 AND obj_commented IS NOT NULL AND obj_commented > now() - '7days'::interval AND obj_commented > p2o_notified AND p2o_notify = 't'; QUERY PLAN Merge Join (cost=6546.54..6584.69 rows=3 width=24) (actual time=86.262..1349.266 rows=1770 loops=1) Merge Cond: (person2obj_counters.p2oc_id = person2obj.p2o_id) Buffers: shared hit=1140491 -> Index Scan using pk_person2obj_counters on person2obj_counters (cost=0.00..47110.95 rows=1212591 width=8) (actual time=0.008..997.948 rows=1212765 loops=1) Buffers: shared hit=1108452 -> Sort (cost=6546.42..6546.98 rows=221 width=24) (actual time=85.877..88.373 rows=7870 loops=1) Sort Key: person2obj.p2o_id Sort Method: quicksort Memory: 807kB Buffers: shared hit=32039 -> Nested Loop (cost=0.00..6537.82 rows=221 width=24) (actual time=0.097..80.129 rows=7870 loops=1) Buffers: shared hit=32039 -> Index Scan using i_blog_post_commented_active on blog_post obj (cost=0.00..225.73 rows=1726 width=16) (actual time=0.028..17.957 rows=6010 loops=1) Index Cond: ((obj_commented IS NOT NULL) AND (obj_commented > (now() - '7 days'::interval))) Buffers: shared hit=6207 -> Index Scan using i_person2obj_obj_notified_subscribed on person2obj (cost=0.00..3.64 rows=1 width=24) (actual time=0.006..0.009 rows=1 loops=6010) Index Cond: ((person2obj.p2o_obj_obj_id = obj.obj_id) AND (obj.obj_commented > person2obj.p2o_notified)) Buffers: shared hit=25832 Total runtime: 1349.767 ms I don't understand why database choose merge join with 1.2M entries table. person2obj_counters have an index on p2oc_id (it's a primary key field). Switch to fast inner loop plan could be managed with set random_page_cost=10 db=# set random_page_cost to 10; SET db=# EXPLAIN (ANALYZE, COSTS, BUFFERS) select obj_id, obj_commented,p2o_id FROM blog_post as obj JOIN person2obj ON p2o_obj_obj_id = obj_id JOIN person2obj_counters ON p2oc_id = p2o_id WHERE obj_status_did = 1 AND obj_commented IS NOT NULL AND obj_commented > now() - '7days'::interval AND obj_commented > p2o_notified AND p2o_notify = 't'; QUERY PLAN -- Nested Loop (cost=0.00..14810.38 rows=3 width=24) (actual time=16.910..115.110 rows=1758 loops=1) Buffers: shared hit=57403 -> Nested Loop (cost=0.00..14616.37 rows=221 width=24) (actual time=0.088..82.342 rows=7858 loops=1) Buffers: shared hit=32046 -> Index Scan using i_blog_post_commented_active on blog_post obj (cost=0.00..273.70 rows=1725 width=16) (actual time=0.029..16.260 rows=6009 loops=1) Index Cond: ((obj_commented IS NOT NULL) AND (obj_commented > (now() - '7 days'::interval))) Buffers: shared hit=6222 -> Index Scan using i_person2obj_obj_notified_subscribed on person2obj (cost=0.00..8.30 rows=1 width=24) (actual time=0.007..0.010 rows=1 loops=6009) Index Cond: ((person2obj.p2o_obj_obj_id = obj.obj_id) AND (obj.obj_commented > person2obj.p2o_notified)) Buffers: shared hit=25824 -> Index Scan using pk_person2obj_counters on person2obj_counters (cost=0.00..0.87 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=7858) Index Cond: (person2obj_counters.p2oc_id = person2obj.p2o_id) Buffers: shared hit=25357 Total runtime: 115.465 ms (14 rows) Ok... so what I see... the database think it will need join 221 rows from previous level to the person2obj_counters table. And somehow Pg manage to calculate that the full index scan over 1.2M entries of the person2obj_counters table is faster that nested loop probes over 221 value. What look very suspicious is that merge full index scan+merge join part adds only 40 points to the total cost ( Merge Join (cost=6546.54..6584.69 rows=3 width=24) (actual time=86.262..1349.266 rows=1770 loops=1) ... -> Index Scan using pk_person2obj_counters on person2obj_counters (cost=0.00..47110.95 rows=1212591 width=8) (actual time=0.008..997.948 rows=1212765 loops=1) ... -> Sort (cost=6546.42..6546.98 rows=221 width=24) (actual time=85.877..88.373 rows=7870 loops=1) )... how that could be? -- Maxim Boguk Senior Postgresql DBA. Phone RU: +7 910 4
[GENERAL] measure time intervals
Hi, I'm wondering ig it is possible to measure elapsed time between 2 particular queries in PostgreSQL. what I need is the equivalent of @@TIMETICKS in Transac-SQL or CLOCK_MONOTONIC in Unix or GetTickCount in Windows These are not affected by system time changes, so time interval can be calculated even if the system time is changed by NTP or the user. That's why I can't use any function based on system time.
[GENERAL] 9.1.3 Standby catchup mode
I am wondering how the catchup mode of a hot synchron slave server works on 9.1.3 if there is no WAL archive. Can the slave only request WALs that are still in the xlog directory of the master server? Or does the master regenerate some kind of fake log for the catchup mode? E.g. in case of a slave failure I could use a weekly backup and let the catchup mode do the rest? Or does that only work if you use WAL archive? The Doc says the following: "When a standby first attaches to the primary, it will not yet be properly synchronized. This is described as catchup mode. Once the lag between standby and primary reaches zero for the first time we move to real-time streaming state. The catch-up duration may be long immediately after the standby has been created." I sounds as if the catchup mode has got magic powers, but I don't know if I'm readying the bible correctly. -- NEU: FreePhone 3-fach-Flat mit kostenlosem Smartphone! Jetzt informieren: http://mobile.1und1.de/?ac=OM.PW.PW003K20328T7073a -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1.3 Standby catchup mode
On 04/05/2012 09:35 AM, hans wulf wrote: > I am wondering how the catchup mode of a hot synchron slave server works on > 9.1.3 if there is no WAL archive. http://www.postgresql.org/docs/9.1/interactive/warm-standby.html#STREAMING-REPLICATION "Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they're generated, without waiting for the WAL file to be filled." > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question about alternate ordering of results
haman...@t-online.de writes: > Now, in versions 8 and later the "using <&-" is rejected, > the ordering op "needs to be < or > member of a btree operator class". > What is needed to create the old behaviour again > - create a complete operator class, including new names for the unchanged > equals/not equals function? Yes. It sounds like you have pretty much all the spare parts you need, you just have to collect them together into an opclass for each ordering you want. > Is this relevant to performance? Somewhat, in that it helps the planner optimize ordering considerations. But IIRC the main argument for tightening it up was to catch mistakes wherein somebody says "ORDER BY x USING &&", or some other operator that doesn't produce a consistent sort order. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] EDB - oracle compatibility (Nested Tables)
What abt the compatibility w.r.to PostgreSQL? -- View this message in context: http://postgresql.1045698.n5.nabble.com/EDB-oracle-compatibility-Nested-Tables-tp5620654p5622857.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using DEFAULT as a parameter value with PQexecPrepare()
I simplified my example somewhat. I usually have six of these "optional" parameters. The number of prepared statements would be too many for this approach. I will follow your advice in the cases where I just one or two of the "optional" parameters. Looks like I will need to dynamically build my SQL command as: INSERT INTO t ( c1, c2, c5, c8, c11 ) VALUES ( $1, $2, $3, $4, $5 ); when the number of "optional" parameters is larger. I can at least use PQexecParams() to get some SQL injection protection and avoid the escaping and quoting of the parameter values. Thanks for the advice -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Greg Sabino Mullane Sent: Thursday, April 05, 2012 7:18 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Using DEFAULT as a parameter value with PQexecPrepare() -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I was looking for a to use a prepared statement for this operation. > > PREPARE myinsert AS "INSERT INTO t ( c1, c2, c3, c4, c5) VALUES ( $1, $2, > $3, $4, $5); > > Now I want to execute this prepared statement something like: > > EXECUTE myinsert ( 'abc', 1, DEFAULT, 9, 3); > > Is there any way to specify the column's default value as a parameter value? You will need to have separate prepared statements. In the case above, you can use either: PREPARE myinsert2 AS INSERT INTO t(c1,c2,c3,c4,c5) VALUES ($1,$2,DEFAULT,$3,$4); or PREPARE myinsert2 AS INSERT INTO t(c1,c2,c4,c5) VALUES ($1,$2,$3,$4); - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201204052214 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk9+UiQACgkQvJuQZxSWSsikAwCg/f28B4vLzPvurQtf8hmdhqO4 dHgAoIR8nuy89zN3t46FdoQMDm3oWIE3 =wCLM -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] measure time intervals
On 04/05/12 7:00 AM, Vincent Dautremont wrote: These are not affected by system time changes, so time interval can be calculated even if the system time is changed by NTP or the user. That's why I can't use any function based on system time. properly configured Unix NTP doesn't step-change the clock, it very gently slows it down or speeds it up until it is accurate and maintains stability. and only root can change the clock on a Unix system, so this really shouldn't be a concern. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] EDB - oracle compatibility (Nested Tables)
On 04/06/12 7:21 AM, cognizant wrote: What abt the compatibility w.r.to PostgreSQL? community postgresql has no pretentions of being Oracle compatible. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using DEFAULT as a parameter value with PQexecPrepare()
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I simplified my example somewhat. I usually have six of > these "optional" parameters. The number of prepared statements > would be too many for this approach. > I will follow your advice in the cases where I just one or two > of the "optional" parameters. Well, it shouldn't be too bad if you can build them dynamically and let the app track them, e.g. a hash/LL with the column names smushed together. A little more work, but worth it if you are calling these often. ... > I can at least use PQexecParams() to get some SQL injection > protection and avoid the escaping and quoting of the parameter values. One other way I should mention is that if your app knows it, it can always pass in the default value(s) directly. :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201204061612 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk9/TpQACgkQvJuQZxSWSsjRdwCdEjDz0K54rNlwb+nECXoT1TMB VvIAn325b3Sjcag0MqaiPtsPpm+Q1/zj =aZDP -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PANIC: corrupted item pointer
On Sat, 2012-03-31 at 13:21 +0200, Janning Vygen wrote: > The OS was installed a few days before, the i installed the postgresql > instance. I configured my setup with a backup server by WAL archiving. > Then i tested some things and i played around with pg_reorg (but i > didn't use ist till then) then i dropped the database, shut down my app, > installed a fresh dump and restarted the app. Hmm... I wonder if pg_reorg could be responsible for your problem? I know it does a few tricky internal things. > Is it still worth to make the copy now? At the moment everything is > running fine. Probably not very useful now. > No, i didn't found any in my postgresql dirs. Should i have a core file > around when i see a segmentation fault? What should i look for? It's an OS setup thing, but generally a crash will generate a core file if it is allowed to. Use "ulimit -c unlimited" on linux in the shell that starts postgresql and I think that will work. You can test it by manually doing a "kill -11" on the pid of a backend process. > I have never done it before. But as everything runs fine at the moment > it's quite useless, isn't it? I meant a backtrace from the core file. If you don't have a core file, then you won't have this information. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1.3 Standby catchup mode
On Thu, Apr 5, 2012 at 12:35 PM, hans wulf wrote: > I am wondering how the catchup mode of a hot synchron slave server works > on 9.1.3 if there is no WAL archive. > Why would you not want to maintain a WAL archive? Are you depending on the slave server(s) as your only form of backup? It isn't clear what you want from synchronous streaming replication, or if you understand the difference between synchronous streaming replication and asynchronous streaming replication. -- Mike Nolan
