[PERFORM] Using more tha one index per table
Hi, I have a question concerning the uses of indexes in Postgresql. I red that in PG a query can not use more than one index per table: "a query or data manipulation command can use at most one index per table". Actually I found this a little weird and unfortunately I could not find further explanation with my Google searches. But the tests I made proved that this is true: If we have table : *create table test_index(col_1 integer, col_2 integer, col_3 integer, col_4 integer)* and we have 2 indexes created on this table: *create index idx_col_1 on test_index (col_1)* *create index idx_col_2 on test_index (col_2)* A query like : *select * from test_index where col_1 = 15 and col_2 = 30* would never use both the indexes. The query plan is: *"Index Scan using idx_col_2 on test_index (cost=0.00..8.27 rows=1 width=16) (actual time=0.092..0.092 rows=0 loops=1)"* *" Index Cond: (col_2 = 30)"* *" Filter: (col_1 = 15)"* *"Total runtime: 0.127 ms"* The query will use *idx_col_2 *only and apply the other condition ignoring the other index(*idx_col_1*). So please can you give some more details about this point. Is the above citation true or I misunderstood it? A next step is what if a query made a join on two tables table1 and table2 (for ex: where table1.id = table2.id and table2.col_2 = 3 and table2.col_3 = 4)? Will it use, for table2, the index of the join column (table2.id) only and neglect the indexes of the other two columns(col_2 and col_3) although they are present in the where clause. Thanks for your response, Elias
[PERFORM] Queries with conditions using bitand operator
Hi, I have table "ARTICLE" containing a String a field "STATUS" that represents a number in binary format (for ex: 10011101). My application issues queries with where conditions that uses BITAND operator on this field (for ex: select * from article where status & 4 = 4). Thus i'm facing performance problemes with these select queries: the queries are too slow. Since i'm using the BITAND operator in my conditions, creating an index on the status filed is useless and since the second operator variable (status & 4 = 4; status & 8 = 8; status & 16 = 16...) a functional index is also usless (because a functional index require the use of a function that accept only table column as input parameter: constants are not accepted). So is there a way to enhance the performance of these queries? Thanks, Elias -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Fwd: Dead lock
Hi,
Actually i guess the problem is related to the way PG uses to aquire
lock on the rows that will be updated.
Suppose the update query will affect 5 rows: A, B, C, D and E.
Apparently the folowing senario is happening:
1- Transaction1 locks row A
2- Trnasaction2 locks row B
3- Transaction1 updates row A
4- Tranasaction2 updates row B
5- Transaction1 *tries *to acquire lock on row B(and fail because
row B is still locked by transaction2)
6- Transaction2 *tries *to acquire lock on row A(and fail because
row A is still locked by transaction1)
Hence the dead lock.
Is this a plausible explanation of what is going on?
If yes, what can be done to avoid the dead lock?
Thanks again.
Original Message
Subject:Dead lock
Date: Mon, 14 Jun 2010 14:50:43 +0300
From: Elias Ghanem
To: pgsql-performance@postgresql.org
Hi all,
I have 2 data bases trying to perform an update query at the same time
on a same table in a third data base using db link.
I'm getting a dead lock exception:
ERROR: deadlock detected
DETAIL: Process 27305 waits for ShareLock on transaction 55575; blocked
by process 27304.
Process 27304 waits for ShareLock on transaction 55576; blocked by
process 27305.
HINT: See server log for query details.
Actually the folowing function is installed on 2 dbs DB1 and DB2. This
function issues an update query on DB3.
When this function is running simultaneously on DB1 and DB2, it produces
a dead lock making one of the functions (in DB1 or DB2) stop with the
above exception:
Is it normal? should'nt postgres be able to handle such situations, for
ex: let one transaction wait untill the other commits or rollback then
continue with the first transaction?
Is there a parameter that should be set in postgresql.conf to allow
handling of concurrent transaction...?
CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)
RETURNS VOID AS'
DECLARE
C INTEGER;
P ALIAS FOR $1;
DUMMY VARCHAR;
BEGIN
C:= 0;
LOOP
EXIT WHEN C > 15;
C:= C+1;
SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3',
''UPDATE IN_FICHE_PRODUIT SET VALIDE = 1'');
RAISE NOTICE ''%, %'', C,P;
END LOOP;
END;'
LANGUAGE 'plpgsql';
Thanks for your time.
[PERFORM] Dead lock
Hi all,
I have 2 data bases trying to perform an update query at the same time
on a same table in a third data base using db link.
I'm getting a dead lock exception:
ERROR: deadlock detected
DETAIL: Process 27305 waits for ShareLock on transaction 55575; blocked
by process 27304.
Process 27304 waits for ShareLock on transaction 55576; blocked by
process 27305.
HINT: See server log for query details.
Actually the folowing function is installed on 2 dbs DB1 and DB2. This
function issues an update query on DB3.
When this function is running simultaneously on DB1 and DB2, it produces
a dead lock making one of the functions (in DB1 or DB2) stop with the
above exception:
Is it normal? should'nt postgres be able to handle such situations, for
ex: let one transaction wait untill the other commits or rollback then
continue with the first transaction?
Is there a parameter that should be set in postgresql.conf to allow
handling of concurrent transaction...?
CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)
RETURNS VOID AS'
DECLARE
C INTEGER;
P ALIAS FOR $1;
DUMMY VARCHAR;
BEGIN
C:= 0;
LOOP
EXIT WHEN C > 15;
C:= C+1;
SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3',
''UPDATE IN_FICHE_PRODUIT SET VALIDE = 1'');
RAISE NOTICE ''%, %'', C,P;
END LOOP;
END;'
LANGUAGE 'plpgsql';
Thanks for your time.
[PERFORM] Is DBLINK transactional
Hi, I am using dblink to read data from a remote data base, insert these data in the local database, update the red data in the remote database then continue to do some other work on the local database in the same transaction. My question is : Is db link transactional; If the local transaction failed, would the update in the remote data base roll back or if the update in the remote data base failed, would the insert in the local data base roll back. If not, is there a way to make db link "transactional"? Thanks
Re: [PERFORM] Multiple data base on same server
Ok thanks guys for your time -Original Message- From: Craig James [mailto:craig_ja...@emolecules.com] Sent: Friday, February 26, 2010 4:34 PM To: Richard Huxton Cc: elias ghanem; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Multiple data base on same server Richard Huxton wrote: > On 26/02/10 12:45, elias ghanem wrote: >> Hi, >> Thanks for your answer, >> Concerning the second point, each db have different table that are >> logically >> related (for ex, tables for configuration, tables for business...) >> plus I'm >> planning to put the indexes on their own tablespaces. >> Concerning the disks I will maybe stored on multiple disks (but surely >> not >> 200-300). So I'm just wondering If this big number of tablespaces on a >> same >> db server may cause problems, > > If the tablespaces aren't on different disks, I'm not sure what the > point is. Our policy is that *every* database has its own tablespace. It doesn't cost you anything, and it gives you great flexibility if you add new disks. You can easily move an entire database, or a bunch of databases, by just moving the data pointing to the new location with symlinks. Once you put a bunch of databases into a single tablespace, moving subsets of them becomes very difficult. It also makes it really easy to find who is using resources. We operate about 450 databases spread across several servers. Postgres has no trouble at all managing hundreds of databases. Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Multiple data base on same server
Hi, Thanks for your answer, Concerning the second point, each db have different table that are logically related (for ex, tables for configuration, tables for business...) plus I'm planning to put the indexes on their own tablespaces. Concerning the disks I will maybe stored on multiple disks (but surely not 200-300). So I'm just wondering If this big number of tablespaces on a same db server may cause problems, Thanks again. -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Richard Huxton Sent: Friday, February 26, 2010 1:44 PM To: elias ghanem Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Multiple data base on same server On 26/02/10 09:37, elias ghanem wrote: > Hi, > > I'm using postgresql 8.4 > > I need to install multiple postgresql dbs on one server but I have some > questions: > > -Is there any problems (performance wise or other) if I have 10 to 15 DBs on > the same server? Clearly that's going to depend on what they're all doing and how big a server you have. There's no limitation in PostgreSQL that stops you though. > -Each DB needs 10 tablespaces, so if I create 10 different tablespaces for > each DB I will have 100 to 150 table space on the same server. So can this > also cause any problems? Do you have 200-300+ disks to put these tablespaces on? If not, I'm not clear what you are trying to do. Why does each DB need 10 tablespaces? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Multiple data base on same server
Hi, I'm using postgresql 8.4 I need to install multiple postgresql dbs on one server but I have some questions: -Is there any problems (performance wise or other) if I have 10 to 15 DBs on the same server? -Each DB needs 10 tablespaces, so if I create 10 different tablespaces for each DB I will have 100 to 150 table space on the same server. So can this also cause any problems? Thanks
[PERFORM] Slow update query
Hi, For the explain analyze here's the output: "Seq Scan on in_sortie (cost=0.00..171140.19 rows=114449 width=84) (actual time=15.074..28461.349 rows=99611 loops=1)" " Output: type, site_id, fiche_produit_id, numero_commande, ligne_commande, date_sortie, quantite_sortie, date_livraison_souhaitee, quantite_souhaitee, client_ref, valeur, type_mouvement, etat_sortie_annulation, etat_sortie_prevision, etat_sortie_taux_service, date_commande, valide" " Filter: (valeur < 0.83)" "Total runtime: 104233.651 ms" (Although the total runtime is 104233.651 ms when I run the query it takes 2.5 mins) -Concerning the exact version of postgresql I'm using, here is the result of the select version() : PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 32-bit - for the postgresql.conf I've attached the file. -Concerning the query, I'm sorry; it seems that I did not explain the problem clearly enough. Here's a better explanation: This update, shown below, is just one step in a long process. After processing certain rows, these rows have to be flagged so they don't get processed another time. UPDATE IN_SORTIE SET VALIDE = 'O' WHERE VALEUR < 0.83 The [SET VALIDE = 'O'] merely flags this row as already processed. The where clause that identifies these rows is rather simple: [WHERE VALEUR < 0.83]. It affects around 100,000 records in a table that contains around 3,000,000. We are running this process on both Oracle and Postgres. I have noticed that this particular UPDATE statement for the same table size and the same number of rows affected, takes 11 seconds on Oracle while it takes 2.5 minutes on Postgres. Knowing that there are no indexes on either database for this table; So the problem can be resumed by the following: why a query like UPDATE IN_SORTIE SET VALIDE = 'O' WHERE VALEUR < 0.83 takes 2.5 min on Postgresql knowing that it is issued on a table containing around 3 000 000 records and affects around 1 00 000 record Thanks again for your advise postgresql.conf Description: Binary data -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Slow update query
Hi, Thanks for your help, here's more details as you requested: -The version of postgres is 8.4 (by the way select pg_version() is not working but let's concentrate on the query issue) Here's the full definition of the table with it's indices: -- Table: in_sortie -- DROP TABLE in_sortie; CREATE TABLE in_sortie ( "type" character(1), site_id character varying(100), fiche_produit_id character varying(100), numero_commande character varying(100), ligne_commande integer, date_sortie date, quantite_sortie numeric(15,2), date_livraison_souhaitee date, quantite_souhaitee numeric(15,2), client_ref character varying(100), valeur numeric(15,2), type_mouvement character varying(100), etat_sortie_annulation integer, etat_sortie_prevision integer, etat_sortie_taux_service integer, date_commande date, valide character varying(1) ) WITH ( OIDS=FALSE ) TABLESPACE "AG_INTERFACE"; -- Index: idx_in_sortie -- DROP INDEX idx_in_sortie; CREATE INDEX idx_in_sortie ON in_sortie USING btree (site_id, fiche_produit_id); -- Index: idx_in_sortie_fp -- DROP INDEX idx_in_sortie_fp; CREATE INDEX idx_in_sortie_fp ON in_sortie USING btree (fiche_produit_id); -- Index: idx_in_sortie_site -- DROP INDEX idx_in_sortie_site; CREATE INDEX idx_in_sortie_site ON in_sortie USING btree (site_id); -Concerning the postgresql.conf file I've tried to changed the default values such as: shared_buffers and effective_cache_size. but this did not change the result. -The WAL IS NOT ON DIFFERENT DISK, THEY ARE ON THE SAME DISK WHER THE DB IS (for the moment I don't have the possibility of moving them to another disk but maybe "just for testing" you can tell me how I can totally disable WAL if possible). I'm using postgresql 8.4 on Linux machine with 1.5 GB RAM, and I'm issuing an update query with a where clause that updates approximately 100 000 rows in a table containing approximately 3 200 000 rows. The update query is very simple: UPDATE IN_SORTIE SET VALIDE = VALIDE WHERE VALEUR < 0.83 (the where clause is used to limit the affected rows to ~ 100 000, and the "SET VALIDE = VALIDE" is only on purpose to keep the data of the table unchanged). Actually this query is inside a function and this function is called from a .sh file using the following syntax: psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER -c "SELECT testupdate()" (the function is called 100 times with a vacuum analyze after each call for the table). So the average execution time of the function is around 2.5 mins, meaning that the update query (+ the vacuum) takes 2.5 mins to execute. So is this a normal behavior? (The same function in oracle with the same environment (with our vacuum obviously) is executed in 11 second). Thanks for your help.
[PERFORM] Slow update query
Hi, I'm not sure this is the right place to ask my question, so please if it is not let me know where I can get an answer from. I'm using postgresql 8.4 on Linux machine with 1.5 GB RAM, and I'm issuing an update query with a where clause that updates approximately 100 000 rows in a table containing approximately 3 200 000 rows. The update query is very simple: UPDATE TABLE1 SET FIELD1 = FIELD1 WHERE FIELD2 < 0.83 (the where clause is used to limit the affected rows to ~ 100 000, and the "SET FIELD1 = FIELD1" is only on purpose to keep the data of the table unchanged). Actually this query is inside a function and this function is called from a .sh file (the function is called 100 times with a vacuum analyze after each call for the table). So the average execution time of the function is around 2.5 mins, meaning that the update query (+ the vacuum) takes 2.5 mins to execute. So is this a normal behavior? (The same function in oracle with the same environment (with our vacuum obviously) is executed in 11 second). Note that no index is created on FIELD2 (neither in postgresql nor in oracle) Thanks for your help.
