Re: [GENERAL] Postgres 9.0 Streaming Replication and Load Balancing?
Unsure you can achieve this without a read only and a read write application set up, I've always had RW application servers separate from RO ones. You could disable the application connection pool completely and let pg-pool do the pooling for you (not sure of performance impact, if any) as the session needs to be terminated after an insert for pg-pool to load balance correctly (see http://www.pgpool.net/mediawiki/index.php/FAQ, 5th one down for more details). - Sumit On 16 May 2012 16:34, Paulo Correia paulo.corr...@pdmfc.com wrote: Hello Sumit, At the given point there are no exceptions since the tests for using pgPool-II with the application using a master and a slave resulted in all connections being done on the master and none on the slave. As the application as it's own connection pool, eventually all connections will have a RW operation and as so all these connections will be linked to the master. As so, I cannot benefit from load balancing since all my connections will be redirected to the master by pgPool-II, even if they are RO. Is it possible to have pgPool-II making only load balance with no connection pool? Best regards, Paulo Correia On 15/05/12 09:01, Sumit Raja wrote: On 14 May 2012 17:28, Paulo Correiapaulo.corr...@pdmfc.com wrote: Hello all! Having a Postgres 9.0 with assynchronous streaming replication to a hot-standby slave, both with CentOs 5.6, how can I use both DB instances for query load balancing? I've tried with pgPool-II but the pooling mechanism is disruptive with the existing pool on the application servers. Is the application RW or read only? What is the disruption being caused? Are you seeing specific exceptions? - Sumit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Raja Consulting Ltd. Incorporated in England and Wales No. 06454814, Registered Office: 4 Calder Court, Shorebury Point, Amy Johnson Way, Blackpool FY4 2RH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is EXISTS the most efficient approach for PostgreSql to check for existence of nodes in a tree?
I have a key value table in my Postgresql db, which represents hierarchical data through parent_feature_mapping column that points to id of feature_mapping_id column of the same table. I need to select root nodes that has children which satisfy various conditions. The conditions may extend to children of children, so I'm trying to find roots of trees that contain paths that satisfy the given constraints. An example is finding the trees where the root node has type 'COMPOSITION' and root node's archetypeNodeId attribute has value 'openEHR-EHR-COMPOSITION.discharge.v1' another constraint is root node having a child of type 'CONTENTITEM' that in turn has a child of type 'ITEMSTRUCTURE' All nodes in a tree have the same payload Id. The fastest query that I could write so far is given below. SELECT root.id from path_value as root WHERE root.rm_type_name = 'COMPOSITION' AND root.feature_name = 'composition' AND EXISTS (SELECT 1 from path_value as anodeid WHERE anodeId.parent_feature_mapping_id = root.feature_mapping_id AND anodeId.payload_id = root.payload_id AND anodeId.feature_name = 'archetypeNodeId' AND anodeId.val_string = 'openEHR-EHR-COMPOSITION.discharge.v1' LIMIT 1 ) AND EXISTS (SELECT 1 from path_value as node1 WHERE node1.payload_id = root.payload_id AND node1.parent_feature_mapping_id = root.feature_mapping_id AND node1.feature_name = 'content' AND node1.rm_type_name = 'CONTENTITEM' AND EXISTS (SELECT 1 from path_value as node2 WHERE node2.payload_id = node1.payload_id AND node2.parent_feature_mapping_id = node1.feature_mapping_id AND node2.rm_type_name = 'ITEMSTRUCTURE' LIMIT 1) LIMIT 1) My question is: is this the best approach in terms of performance? This is an attempt to identify XML payloads that fit certain criteria. I have also considered using an ltree column that will contain the tree in a from that I can query as an alternative to sql based method, or I can use xpath queries on XML payload. The create statement for my table is as follows: CREATE TABLE public.path_value ( val_string TEXT, feature_mapping_id INTEGER NOT NULL, parent_feature_mapping_id INTEGER, feature_name TEXT, rm_type_name TEXT, path INTEGER NOT NULL, payload_id INTEGER NOT NULL, id INTEGER NOT NULL, ehr_id INTEGER, CONSTRAINT path_value_pkey PRIMARY KEY(id) ) WITHOUT OIDS; Best regards Seref
Re: [GENERAL] Is EXISTS the most efficient approach for PostgreSql to check for existence of nodes in a tree?
On 05/17/2012 03:06 AM, Seref Arikan wrote: I have a key value table in my Postgresql db, which represents hierarchical data through parent_feature_mapping column that points to id of feature_mapping_id column of the same table. I need to select root nodes that has children which satisfy various conditions. The conditions may extend to children of children, so I'm trying to find roots of trees that contain paths that satisfy the given constraints. An example is finding the trees where the root node has type 'COMPOSITION' and root node's archetypeNodeId attribute has value 'openEHR-EHR-COMPOSITION.discharge.v1' another constraint is root node having a child of type 'CONTENTITEM' that in turn has a child of type 'ITEMSTRUCTURE' All nodes in a tree have the same payload Id. The fastest query that I could write so far is given below. SELECT root.id http://root.id from path_value as root WHERE root.rm_type_name = 'COMPOSITION' AND root.feature_name = 'composition' AND EXISTS (SELECT 1 from path_value as anodeid WHERE anodeId.parent_feature_mapping_id = root.feature_mapping_id AND anodeId.payload_id = root.payload_id AND anodeId.feature_name = 'archetypeNodeId' AND anodeId.val_string = 'openEHR-EHR-COMPOSITION.discharge.v1' LIMIT 1 ) AND EXISTS (SELECT 1 from path_value as node1 WHERE node1.payload_id = root.payload_id AND node1.parent_feature_mapping_id = root.feature_mapping_id AND node1.feature_name = 'content' AND node1.rm_type_name = 'CONTENTITEM' AND EXISTS (SELECT 1 from path_value as node2 WHERE node2.payload_id = node1.payload_id AND node2.parent_feature_mapping_id = node1.feature_mapping_id AND node2.rm_type_name = 'ITEMSTRUCTURE' LIMIT 1) LIMIT 1) My question is: is this the best approach in terms of performance? This is an attempt to identify XML payloads that fit certain criteria. I have also considered using an ltree column that will contain the tree in a from that I can query as an alternative to sql based method, or I can use xpath queries on XML payload. The create statement for my table is as follows: CREATE TABLE public.path_value ( val_string TEXT, feature_mapping_id INTEGER NOT NULL, parent_feature_mapping_id INTEGER, feature_name TEXT, rm_type_name TEXT, path INTEGER NOT NULL, payload_id INTEGER NOT NULL, id INTEGER NOT NULL, ehr_id INTEGER, CONSTRAINT path_value_pkey PRIMARY KEY(id) ) WITHOUT OIDS; Best regards Seref Any other constraints or indexes on that table?
Re: [GENERAL] Postgres 9.0 Streaming Replication and Load Balancing?
Yes, that was the results of our tests ... It seems we'll have to do a lot of work on the application to separate the queries in order to achieve the load-balancing. Thanks anyway, Best regards, Paulo Correia On 17/05/12 09:32, Sumit Raja wrote: Unsure you can achieve this without a read only and a read write application set up, I've always had RW application servers separate from RO ones. You could disable the application connection pool completely and let pg-pool do the pooling for you (not sure of performance impact, if any) as the session needs to be terminated after an insert for pg-pool to load balance correctly (see http://www.pgpool.net/mediawiki/index.php/FAQ, 5th one down for more details). - Sumit On 16 May 2012 16:34, Paulo Correiapaulo.corr...@pdmfc.com wrote: Hello Sumit, At the given point there are no exceptions since the tests for using pgPool-II with the application using a master and a slave resulted in all connections being done on the master and none on the slave. As the application as it's own connection pool, eventually all connections will have a RW operation and as so all these connections will be linked to the master. As so, I cannot benefit from load balancing since all my connections will be redirected to the master by pgPool-II, even if they are RO. Is it possible to have pgPool-II making only load balance with no connection pool? Best regards, Paulo Correia On 15/05/12 09:01, Sumit Raja wrote: On 14 May 2012 17:28, Paulo Correiapaulo.corr...@pdmfc.comwrote: Hello all! Having a Postgres 9.0 with assynchronous streaming replication to a hot-standby slave, both with CentOs 5.6, how can I use both DB instances for query load balancing? I've tried with pgPool-II but the pooling mechanism is disruptive with the existing pool on the application servers. Is the application RW or read only? What is the disruption being caused? Are you seeing specific exceptions? - Sumit -- 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
[GENERAL] cannot compile www_fdw Foreign Data Wrapper
Hi all, I am trying to compile the www_fdw foreign data wrapper on PostgreSQL 9.2 beta but I am getting the following error: cp sql/www_fdw.sql sql/www_fdw--0.1.0.sql gcc -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I/usr/include/libxml2 -I. -I. -I/usr/include/postgresql/9.2/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5 -c -o src/json_parser.o src/json_parser.c gcc -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I/usr/include/libxml2 -I. -I. -I/usr/include/postgresql/9.2/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5 -c -o src/serialize_quals.o src/serialize_quals.c gcc -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I/usr/include/libxml2 -I. -I. -I/usr/include/postgresql/9.2/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5 -c -o src/www_fdw.o src/www_fdw.c src/www_fdw.c:126:1: error: unknown type name ‘FdwPlan’ src/www_fdw.c: In function ‘www_fdw_handler’: src/www_fdw.c:309:12: error: ‘FdwRoutine’ has no member named ‘PlanForeignScan’ src/www_fdw.c: At top level: src/www_fdw.c:434:1: error: unknown type name ‘FdwPlan’ src/www_fdw.c: In function ‘www_plan’: src/www_fdw.c:437:2: error: unknown type name ‘FdwPlan’ src/www_fdw.c:441:12: error: ‘FdwPlan’ undeclared (first use in this function) src/www_fdw.c:441:12: note: each undeclared identifier is reported only once for each function it appears in src/www_fdw.c:441:12: error: expected expression before ‘)’ token src/www_fdw.c:442:9: error: request for member ‘fdw_private’ in something not a structure or union make: *** [src/www_fdw.o] Error 1 Any ideas what the problem could be? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Is EXISTS the most efficient approach for PostgreSql to check for existence of nodes in a tree?
Trying to reply to Rob: Apologies if this does not end up in the thread (gmail is just driving me mad, I can't seem to receive messages, so I've subscribed again) For some reason Limit 1 cause my query to go on for minutes without a response, which was not the case. The following query takes about 10.6 seconds, with a table that has about 20 million rows. First, the indices of the table with create statement: CREATE TABLE public.path_value ( val_string TEXT, feature_mapping_id INTEGER NOT NULL, parent_feature_mapping_id INTEGER, feature_name TEXT, rm_type_name TEXT, path INTEGER NOT NULL, payload_id INTEGER NOT NULL, id INTEGER NOT NULL, ehr_id INTEGER, CONSTRAINT path_value_pkey PRIMARY KEY(id) ) WITHOUT OIDS; CREATE INDEX path_value_idx ON public.path_value USING btree (rm_type_name COLLATE pg_catalog.default, feature_name COLLATE pg_catalog.default); CREATE INDEX path_value_idx1 ON public.path_value USING btree (feature_mapping_id, parent_feature_mapping_id); CREATE INDEX path_value_idx2 ON public.path_value USING btree (payload_id); Now results of EXPLAIN ANALYZE: QUERY PLAN Nested Loop Semi Join (cost=543422.34..571013.45 rows=5 width=4) (actual time=5854.267..10684.798 rows=82003 loops=1) Join Filter: (root.feature_mapping_id = anodeid.parent_feature_mapping_id) - Merge Join (cost=543422.34..543426.29 rows=12 width=24) (actual time=5854.182..5953.531 rows=82003 loops=1) Merge Cond: ((root.feature_mapping_id = node1.parent_feature_mapping_id) AND (root.payload_id = node2.payload_id)) - Sort (cost=1150.65..1151.35 rows=283 width=12) (actual time=359.940..380.707 rows=82003 loops=1) Sort Key: root.feature_mapping_id, root.payload_id Sort Method: external merge Disk: 1768kB - Index Scan using path_value_idx on path_value root (cost=0.00..1139.12 rows=283 width=12) (actual time=0.073..261.240 rows=82003 loops=1) Index Cond: ((rm_type_name = 'COMPOSITION'::text) AND (feature_name = 'composition'::text)) - Sort (cost=542271.69..542272.31 rows=247 width=12) (actual time=5494.236..5516.788 rows=82003 loops=1) Sort Key: node1.parent_feature_mapping_id, node2.payload_id Sort Method: external sort Disk: 2088kB - HashAggregate (cost=542259.40..542261.87 rows=247 width=12) (actual time=5377.761..5394.453 rows=82003 loops=1) - Hash Semi Join (cost=506297.37..542253.38 rows=1205 width=12) (actual time=2788.758..5325.838 rows=164004 loops=1) Hash Cond: ((node1.payload_id = node2.payload_id) AND (node1.feature_mapping_id = node2.parent_feature_mapping_id)) - Bitmap Heap Scan on path_value node1 (cost=77.61..9421.62 rows=2464 width=12) (actual time=76.628..2430.473 rows=246005 loops=1) Recheck Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text)) - Bitmap Index Scan on path_value_idx (cost=0.00..77.00 rows=2464 width=0) (actual time=75.640..75.640 rows=246005 loops=1) Index Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text)) - Hash (cost=498674.38..498674.38 rows=399092 width=8) (actual time=2711.653..2711.653 rows=410015 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 1013kB - Bitmap Heap Scan on path_value node2 (cost=10345.32..498674.38 rows=399092 width=8) (actual time=71.718..2607.467 rows=410015 loops=1) Recheck Cond: (rm_type_name = 'ITEMSTRUCTURE'::text) - Bitmap Index Scan on path_value_idx (cost=0.00..10245.55 rows=399092 width=0) (actual time=69.634..69.634 rows=410015 loops=1) Index Cond: (rm_type_name = 'ITEMSTRUCTURE'::text) - Index Scan using path_value_idx2 on path_value anodeid (cost=0.00..2298.91 rows=1 width=8) (actual time=0.057..0.057 rows=1 loops=82003) Index Cond: (payload_id = node2.payload_id) Filter: ((feature_name = 'archetypeNodeId'::text) AND (val_string = 'openEHR-EHR-COMPOSITION.discharge.v1'::text)) Total runtime: 10692.277 ms and finaly, the query that gave this result: EXPLAIN ANALYZE SELECT root.id from path_value as root WHERE root.rm_type_name = 'COMPOSITION' AND root.feature_name = 'composition' AND EXISTS (SELECT anodeid.id from path_value as anodeid WHERE anodeId.parent_feature_mapping_id = root.feature_mapping_id AND anodeId.payload_id = root.payload_id AND anodeId.feature_name = 'archetypeNodeId' AND anodeId.val_string = 'openEHR-EHR-COMPOSITION.discharge.v1' )
Re: [GENERAL] cannot compile www_fdw Foreign Data Wrapper
Adrian Schreyer ams...@cam.ac.uk writes: I am trying to compile the www_fdw foreign data wrapper on PostgreSQL 9.2 beta but I am getting the following error: We changed the planner API for foreign data wrappers in 9.2, so you won't be able to compile 9.1 FDWs until their code is updated. 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] difference in query plan when db is restored
No change I'm afraid :( -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 17 May 2012 22:59 To: John Watts Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] difference in query plan when db is restored John Watts jwa...@promotion-update.com writes: I have a database query which executes normal (under 1s) with 21 steps according to the query paln. However, when the database is dumped and restored on the _same_ PostgreSQL server, the query plan takes 34 steps to complete and it executes in excess of 90 seconds! Usually you need to ANALYZE after restoring from a dump ... 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
[GENERAL] How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.
Hi Guys. Please help me about this. For postgres database, it looks like we need analyze and vacuum all the tables periodly. I need to write a script which can be executed in crontab. I don't have any clues about that. I only know the command : analyze tablename; vacuum tablename; Please helps. Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-write-a-script-to-analyze-and-vacuum-all-the-tables-in-the-system-catalog-Thanks-tp5709059.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
[GENERAL] How to debug the performance issues via which system catalog.Thanks.
I know we can know the currenct activity via pg_stat_activity. What's else you guys use to debug. And for some times back, how can we check the activities? Thanks. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-debug-the-performance-issues-via-which-system-catalog-Thanks-tp5709065.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] How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.
On 05/17/2012 11:30 AM, leaf_yxj wrote: Hi Guys. Please help me about this. For postgres database, it looks like we need analyze and vacuum all the tables periodly. I need to write a script which can be executed in crontab. I don't have any clues about that. I only know the command : analyze tablename; vacuum tablename; Leave off the tablename to do the whole db. But (unless you are on a really old version of PG) you really shouldn't need to manually vacuum things as that is the job of the autovacuum process. There are unusual situations that suggest a manual analyze. It should be run right after a cluster for instance. But they are limited special cases. What indicates that you need to vacuum or analyze via cron? Cheers, Steve -- 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] How to debug the performance issues via which system catalog.Thanks.
On 05/17/2012 11:54 AM, leaf_yxj wrote: I know we can know the currenct activity via pg_stat_activity. What's else you guys use to debug. And for some times back, how can we check the activities? Thanks. Grace Performance is a complex enough issue to warrant its own mailing list (CPU type, number of cores, OS, OS settings, PG configuration, disk type/count/filesystem/raid-configuration and so on). Greg Smith's PostgreSQL 9.0 High Performance is worth every penny. While waiting for it to arrive, peruse the pages at http://wiki.postgresql.org/wiki/Performance_Optimization Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replication recovery?
Sorry if this is a dumb question. Feel free to just point me to a doc. I've read a little about Postgres replication and the concept of a master and one or more slaves. If one db is down then you just switch to one that's still running. There's even additional software like pgpool to make the switch easy. But I want to know more about how to resume normal operating mode. For example, I take it that if the master is unavailable then you switch to a slave. The former slave becomes the current master. When the original master is ready to run and network accessible then do you bring it online in slave mode and it syncs automatically with the current master? At which time you're almost back to normal. Once they are back in sync do people typically switch the roles back to the original designation of who's a slave and who's a master? It's not clear to me if the last step is necessary. Well, that's assuming that the master comes back online with all the data it had when it went offline. If it comes back but all data was lost (a worst case scenario) then I assume I have to take the current master offline and use it to repopulate the recovering master from scratch, correct? But... if I have additional slaves then I could just take one of the current slaves offline, use it to rebuild the original master, and then bring both the slave and the reconstructed master (now also a slave) back online and both will sync with the current master. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reasons for postgres processes beeing killed by SIGNAL 9?
Hi, Recently single postgres processes are killed by SIGNAL 9 on our virtual vvmware managed server without any manual interaction - causing lost transactions. Any ideas what could be the reason? Could postmaster the source of the signal? We are running postgreql 8.4.7 on Linux 64-bit. Thank you in advance, Clemens 2012-05-17 19:13:16 BST LOG: Serverprozess (PID 4849) wurde von Signal 9 beendet: Killed 2012-05-17 19:13:16 BST LOG: aktive Serverprozesse werden abgebrochen 2012-05-17 19:13:16 BST khbldb_prod khbl WARNUNG: breche Verbindung ab wegen Absturz eines anderen Serverprozesses 2012-05-17 19:13:16 BST khbldb_prod khbl DETAIL: Der Postmaster hat diesen Serverprozess angewiesen, die aktuelle Transaktion zurückzurollen und die Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet wurde und möglicherweise das Shared Memory verfälscht hat. 2012-05-17 19:13:16 BST LOG: alle Serverprozesse beendet; initialisiere neu 2012-05-17 19:13:16 BST LOG: Datenbanksystem wurde unterbrochen; letzte bekannte Aktion am 2012-05-17 18:59:09 BST 2012-05-17 19:13:16 BST LOG: Datenbanksystem wurde nicht richtig heruntergefahren; automatische Wiederherstellung läuft 2012-05-17 19:13:16 BST LOG: Redo beginnt bei 1/343D5010 2012-05-17 19:13:16 BST LOG: Datensatz mit Länge null bei 1/3445EAA8 2012-05-17 19:13:16 BST LOG: Redo fertig bei 1/3445EA78 2012-05-17 19:13:16 BST LOG: letzte vollständige Transaktion war bei Logzeit 2012-05-17 19:13:13.575598+01 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] difference in query plan when db is restored
Hi folks, I have a database query which executes normal (under 1s) with 21 steps according to the query paln. However, when the database is dumped and restored on the _same_ PostgreSQL server, the query plan takes 34 steps to complete and it executes in excess of 90 seconds! Why is the query plan changed and it takes ages to execute? Inceased verbosity in server log file (debug 2) does not disclose anything unusual. Set up detail following - Version PostgreSQL 8.3.0, Linux CentOS 6.2 x86_64 Please not that exactly same behaviour (enhanced) query plan is observed also with current 8.4 and 9.1 PostgreSQL servers, e.g. when restoring the database dump from the 8.3.0 PostgreSQL server. Kind regards, JW Dump and restore - $ pg_dump -U postgres mydb -C mydb.sql (mydb renamed to mydb_old) $ psql -U postgres -f ./mydb.sql mydb.log (no error|warning msgs) [...] -- -- PostgreSQL database dump -- SET client_encoding = 'SQL_ASCII'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; -- -- Name: mydb; Type: DATABASE; Schema: -; Owner: postgres -- CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII'; (changed to mydb) ALTER DATABASE mydb OWNER TO postgres; (changed to mydb) \connect mydb SET client_encoding = 'SQL_ASCII'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; -- -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: dbadmin -- CREATE PROCEDURAL LANGUAGE plpgsql; ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO dbadmin; -- -- Name: plpythonu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: dbadmin -- CREATE PROCEDURAL LANGUAGE plpythonu; ALTER PROCEDURAL LANGUAGE plpythonu OWNER TO dbadmin; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: tbluser; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- [...] Query plan against original 'mydb'. QUERY PLAN -- Sort (cost=245.97..245.98 rows=3 width=373) (actual time=89.289..90.289 rows=3445 loops=1) Sort Key: tblappliccomp.idnumber2, tblappliccomp.idnumber Sort Method: quicksort Memory: 962kB - Nested Loop Left Join (cost=0.00..245.95 rows=3 width=373) (actual time=0.160..80.279 rows=3445 loops=1) - Nested Loop Left Join (cost=0.00..136.01 rows=3 width=175) (actual time=0.079..24.832 rows=3445 loops=1) - Index Scan using tblappliccompindapplicid on tblappliccomp (cost=0.00..111.16 rows=3 width=180) (actual time=0.054..5.901 rows=3445 loops=1) Index Cond: (applicid = 9872) Filter: (((otherr IS NULL) OR (otherr = 0)) AND (inout = 1)) - Index Scan using tbluserindidnumber on tbluser (cost=0.00..8.27 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=3445) Index Cond: (tblappliccomp.usercrid = tbluser.idnumber) - Index Scan using tblcompanyindidnumber on tblcompany (cost=0.00..8.40 rows=1 width=206) (actual time=0.003..0.003 rows=0 loops=3445) Index Cond: (tblappliccomp.companyid = tblcompany.idnumber) SubPlan - Limit (cost=0.00..28.23 rows=1 width=15) (actual time=0.009..0.009 rows=1 loops=3445) - Nested Loop Left Join (cost=0.00..28.23 rows=1 width=15) (actual time=0.008..0.008 rows=1 loops=3445) - Index Scan using leeindapplicid on lee (cost=0.00..19.95 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=3445) Index Cond: (applicid = 70058598) Filter: ((eorg = $0) AND (eevent = $1)) - Index Scan using tblreferralindidnumber on tblreferral (cost=0.00..8.27 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=2947) Index Cond: (lee.referralid = tblreferral.idnumber) Total runtime: 91.636 ms (21 rows) Query plan against restored 'mydb'. QUERY PLAN --- Sort (cost=236436.46..236439.56 rows=1240 width=1205) (actual time=99327.436..99328.512 rows=3445 loops=1) Sort Key: tblappliccomp.idnumber2, tblappliccomp.idnumber Sort Method: quicksort Memory: 962kB - Hash Left Join (cost=178.98..236372.75 rows=1240 width=1205) (actual time=50.351..99300.106 rows=3445 loops=1) Hash Cond: (tblappliccomp.usercrid = tbluser.idnumber) - Nested Loop Left Join (cost=152.95..2001.27 rows=620 width=1165) (actual time=8.778..106.444 rows=3445 loops=1) - Bitmap Heap Scan on tblappliccomp
Re: [GENERAL] difference in query plan when db is restored
John Watts jwa...@promotion-update.com writes: I have a database query which executes normal (under 1s) with 21 steps according to the query paln. However, when the database is dumped and restored on the _same_ PostgreSQL server, the query plan takes 34 steps to complete and it executes in excess of 90 seconds! Usually you need to ANALYZE after restoring from a dump ... 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] Reasons for postgres processes beeing killed by SIGNAL 9?
On 05/17/2012 03:44 PM, Clemens Eisserer wrote: Hi, Recently single postgres processes are killed by SIGNAL 9 on our virtual vvmware managed server without any manual interaction - causing lost transactions. Any ideas what could be the reason? Could postmaster the source of the signal? We are running postgreql 8.4.7 on Linux 64-bit. ... Out of memory or OOM killer?? Any such messages in system logs? Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
