[PERFORM] pg_restore very slow in 8.0.0beta1
Hi, I'm migrating data from 7.4.2 to 8.0.0beta1 and the process is slow (10 15 tuples per second) Can be a type conversion issue? RedS
Re: [PERFORM] I could not get postgres to utilizy indexes
On Thu, 19 Aug 2004 09:54:47 +0200, Leeuw van der, Tim [EMAIL PROTECTED] wrote: You asked the very same question yesterday, and I believe you got some useful answers. Why do you post the question again? Tim, no need to be rude here. We see this effect from time to time when a new user sends a message to a mailing list while not subscribed. The sender gets an automated reply from majordomo, subscribes to the list and sends his mail again. One or two days later the original message is approved (by Marc, AFAIK) and forwarded to the list. Look at the timestamps in these header lines: |Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) | by svr4.postgresql.org (Postfix) with ESMTP id 32B1F5B04F4; | Wed, 18 Aug 2004 15:54:13 + (GMT) |Received: from localhost (unknown [200.46.204.144]) | by svr1.postgresql.org (Postfix) with ESMTP id E6B2B5E4701 | for [EMAIL PROTECTED]; Tue, 17 Aug 2004 11:23:07 -0300 (ADT) [more instructions] And while we are teaching netiquette, could you please stop top-posting and full-quoting. Igor, welcome to the list! Did the suggestions you got solve your problem? Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] using an index worst performances
Gaetano Mendola [EMAIL PROTECTED] writes: Using a prepared query: Without index and default stat 10 :1.12 ms Without index and default stat 1000 : 1.25 ms With index and default stat 10:1.35 ms With index and default stat 1000: 1.6 ms Could we see EXPLAIN ANALYZE EXECUTE output for each case? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Query performance problem
HI All, I have a big performance issue concerning a PostgreSQL database. I have the following server configuration: Pentium 4 2.4 GHz 1 GB RAM 36 GB SCSI And the following tables: TABLES -- == r_cliente: 75816 records CREATE TABLE public.r_cliente ( pkcliente INTEGER NOT NULL, cpfcnpj VARCHAR(20) NOT NULL, PRIMARY KEY(pkcliente) ) WITH OIDS; CREATE UNIQUE INDEX un_cliente_cpfcnpj ON public.r_cliente USING btree (cpfcnpj); == sav_cliente_lg: 65671 records === CREATE TABLE public.sav_cliente_lg ( codigo INTEGER NOT NULL, cpfcnpj VARCHAR(15) NOT NULL, PRIMARY KEY(codigo) ) WITH OIDS; CREATE INDEX ix_savclientelg_cpfcnpj ON public.sav_cliente_lg USING btree (cpfcnpj); Which I would like to run the following query: QUERY -- SELECT rc.pkcliente FROM r_cliente AS rc INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = sc.cpfcnpj; The problem is, it takes a long time to run, I wait up to half an hour and I get no result. So, I executed the explain on the query and got the following results: QUERY PLAN -- Nested Loop (cost=0.00..16696.87 rows=75816 width=4) - Seq Scan on sav_cliente_cf sc (cost=0.00..3047.55 rows=1 width=0) Filter: ((cpfcnpj)::text = (cpfcnpj)::text) - Seq Scan on r_cliente rc (cost=0.00..12891.16 rows=75816 width=4) And made the following modifications on my POSTGRESQL.CONF file: POSTGRESQL.CONF -- ### VERSION: Postgresql 7.4.2 ### shared_buffers = 7800 sort_mem = 4096 checkpoint_segments = 5 effective_cache_size = 12000 cpu_operator_cost = 0.0015 stats_start_collector = false Hope you can help me, I really need to get this running faster, and I am out of ideas. Since now, thanks a lot for your attention, Danilo Mota
Re: [PERFORM] Query performance problem
Danilo Mota [EMAIL PROTECTED] writes: SELECT rc.pkcliente FROM r_cliente AS rc INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = sc.cpfcnpj; Surely you meant INNER JOIN sav_cliente_lg AS sc ON rc.cpfcnpj = sc.cpfcnpj; I would also venture that your statistics are desperately out of date, because if the planner's estimates are close to reality, even this unconstrained-cross-product join shouldn't have taken that long. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Query Performance
Hi all, the following query is working well without the AND on WHERE clause, so I need suggestions about how could I rewrite the query to get the same result with less cost of time and resources. Ive already created indexes on all foreign key columns. Thanks in advance. Danilo Mota SELECT sn.notafiscalnumero, sn.notafiscalserie, CASE sn.notafiscaldata WHEN '' THEN NULL ELSE to_date(sn.notafiscaldata,'MMDD') END, sn.modalidade, rcm.pkclientemarca, sn.notafiscalvalor/100, sn.entrada/100, sn.cliente FROM r_clientemarca AS rcm INNER JOIN r_cliente AS rc ON rc.pkcliente = rcm.fkcliente INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = rc.cpfcnpj INNER JOIN sav_nota_lg AS sn ON sn.cliente = sc.codigo WHERE rcm.fkmarca = 1 AND sn.notafiscalnumero||sn.notafiscalserie||sn.cliente NOT IN ( SELECT numero||serie||codigo FROM r_contrato AS rcon WHERE savfonte = 'lg') TABLES - r_cliente: 75820 records r_clientemarca: 97719 records r_contrato: 782058 records sav_cliente_lg: 65671 records sav_nota_lg: 297329 rcords MY SERVER - Pentium 4 2.4 GHz 1 GB RAM 36 GB SCSI Postgresql 7.4.2 POSTGRESQL.CONF - shared_buffers = 7800 sort_mem = 4096 checkpoint_segments = 5 effective_cache_size = 12000 cpu_operator_cost = 0.0015 stats_start_collector = false QUERY PLAN - Hash Join (cost=27149.61..3090289650.24 rows=128765 width=4) Hash Cond: (outer.cliente = inner.codigo) - Seq Scan on sav_nota_lg sn (cost=0.00..3090258517.99 rows=148665 width=8) Filter: (NOT (subplan)) SubPlan - Seq Scan on r_contrato rcon (cost=0.00..20362.47 rows=282845 width=19) Filter: ((savfonte)::text = 'lg'::text) - Hash (cost=26869.29..26869.29 rows=56880 width=4) - Hash Join (cost=22473.95..26869.29 rows=56880 width=4) Hash Cond: (outer.fkcliente = inner.pkcliente) - Index Scan using ix_r_clientemarca_fkmarca on r_clientemarca rcm (cost=0.00..2244.46 rows=65665 width=4) Index Cond: (fkmarca = 1) - Hash (cost=22118.44..22118.44 rows=65672 width=8) - Hash Join (cost=6613.22..22118.44 rows=65672 width=8) Hash Cond: ((outer.cpfcnpj)::text = (inner.cpfcnpj)::text) - Seq Scan on r_cliente rc (cost=0.00..12891.16 rows=75816 width=23) - Hash (cost=6129.71..6129.71 rows=65671 width=23) - Seq Scan on sav_cliente_lg sc (cost=0.00..6129.71 rows=65671 width=23)
Re: [PERFORM] Query Performance
Have you tried AND (sn.notafiscalnumero, sn.notafiscalserie, sn.cliente) NOT IN ( SELECT numero, serie, codigo FROM r_contrato WHERE savfonte = 'lg') or and not exists(select true from r_contrato where savfonte = 'lg' and numero = sn.notafiscalnumero and serie = sn.notafiscalserie and codigo = sn.cliente) Danilo Mota wrote: Hi all, the following query is working well without the AND on WHERE clause, so I need suggestions about how could I rewrite the query to get the same result with less cost of time and resources. Ive already created indexes on all foreign key columns. Thanks in advance. Danilo Mota SELECT sn.notafiscalnumero, sn.notafiscalserie, CASE sn.notafiscaldata WHEN '' THEN NULL ELSE to_date(sn.notafiscaldata,'MMDD') END, sn.modalidade, rcm.pkclientemarca, sn.notafiscalvalor/100, sn.entrada/100, sn.cliente FROM r_clientemarca AS rcm INNER JOIN r_cliente AS rc ON rc.pkcliente = rcm.fkcliente INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = rc.cpfcnpj INNER JOIN sav_nota_lg AS sn ON sn.cliente = sc.codigo WHERE rcm.fkmarca = 1 AND sn.notafiscalnumero||sn.notafiscalserie||sn.cliente NOT IN ( SELECT numero||serie||codigo FROM r_contrato AS rcon WHERE savfonte = 'lg') TABLES - r_cliente: 75820 records r_clientemarca: 97719 records r_contrato: 782058 records sav_cliente_lg: 65671 records sav_nota_lg: 297329 rcords MY SERVER - Pentium 4 2.4 GHz 1 GB RAM 36 GB SCSI Postgresql 7.4.2 POSTGRESQL.CONF - shared_buffers = 7800 sort_mem = 4096 checkpoint_segments = 5 effective_cache_size = 12000 cpu_operator_cost = 0.0015 stats_start_collector = false QUERY PLAN - Hash Join (cost=27149.61..3090289650.24 rows=128765 width=4) Hash Cond: (outer.cliente = inner.codigo) - Seq Scan on sav_nota_lg sn (cost=0.00..3090258517.99 rows=148665 width=8) Filter: (NOT (subplan)) SubPlan - Seq Scan on r_contrato rcon (cost=0.00..20362.47 rows=282845 width=19) Filter: ((savfonte)::text = 'lg'::text) - Hash (cost=26869.29..26869.29 rows=56880 width=4) - Hash Join (cost=22473.95..26869.29 rows=56880 width=4) Hash Cond: (outer.fkcliente = inner.pkcliente) - Index Scan using ix_r_clientemarca_fkmarca on r_clientemarca rcm (cost=0.00..2244.46 rows=65665 width=4) Index Cond: (fkmarca = 1) - Hash (cost=22118.44..22118.44 rows=65672 width=8) - Hash Join (cost=6613.22..22118.44 rows=65672 width=8) Hash Cond: ((outer.cpfcnpj)::text = (inner.cpfcnpj)::text) - Seq Scan on r_cliente rc (cost=0.00..12891.16 rows=75816 width=23) - Hash (cost=6129.71..6129.71 rows=65671 width=23) - Seq Scan on sav_cliente_lg sc (cost=0.00..6129.71 rows=65671 width=23) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
