Re: [PERFORM] Very slow queries
From: Chad Wagner [EMAIL PROTECTED] To: Sidar López Cruz [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very slow queries Date: Tue, 30 Jan 2007 17:37:17 -0500 On 1/30/07, Sidar López Cruz [EMAIL PROTECTED] wrote: query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Select numero_patrono From ceroriesgo.patronos) Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077 width=6) Filter: (NOT (subplan)) SubPlan - Materialize (cost=51021.78..69422.58 rows=1032980 width=25) - Seq Scan on patronos (cost=0.00..41917.80 rows=1032980 width=25) How many rows exist in salarios, but not in patronos? How many rows are there in salarios? Rows: Patronos: 1032980 Salarios: 28480200 What does the explain look like for: delete from ceroriesgo.salarios s where not exists (select 1 from ceroriesgo.patronos where numero_patrono = s.numero_patrono); Also, is this not a case for a foreign key with a cascade delete? No, this is not cascade delete case because I need to delete from salarios not from patronos. http://www.postgresql.org/docs/8.2/static/ddl-constraints.html -- Chad http://www.postgresqlforums.com/ _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Very slow queries
How many rows were delete last time you ran the query? I never delete any rows, the tables was inserted with copy command, then I create index and I need to delete these records on ceroriesgo.salarios to create the foreign key restriction on it. Chad's query looks good but here is another variation that may help. Delete From ceroriesgo.salarios Where numero_patrono In (Select ceroriesgo.salarios.numero_patrono From ceroriesgo.salarios Left Join ceroriesgo.patronos Using (numero_patrono) Where ceroriesgo.patronos.numero_patrono Is Null) Hope that Helps, Ted Sidar López Cruz wrote: From: Chad Wagner [EMAIL PROTECTED] To: Sidar López Cruz [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very slow queries Date: Tue, 30 Jan 2007 17:37:17 -0500 On 1/30/07, Sidar López Cruz [EMAIL PROTECTED] wrote: query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Select numero_patrono From ceroriesgo.patronos) Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077 width=6) Filter: (NOT (subplan)) SubPlan - Materialize (cost=51021.78..69422.58 rows=1032980 width=25) - Seq Scan on patronos (cost=0.00..41917.80 rows=1032980 width=25) How many rows exist in salarios, but not in patronos? How many rows are there in salarios? Rows: Patronos: 1032980 Salarios: 28480200 What does the explain look like for: delete from ceroriesgo.salarios s where not exists (select 1 from ceroriesgo.patronos where numero_patrono = s.numero_patrono); Also, is this not a case for a foreign key with a cascade delete? No, this is not cascade delete case because I need to delete from salarios not from patronos. http://www.postgresql.org/docs/8.2/static/ddl-constraints.html -- Chad http://www.postgresqlforums.com/ _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend -- *Edward Allen* Software Engineer Black Duck Software, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] T +1.781.891.5100 x133 F +1.781.891.5145 http://www.blackducksoftware.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq _ Las mejores tiendas, los precios mas bajos, entregas en todo el mundo, YupiMSN Compras: http://latam.msn.com/compras/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Very slow queries
From: Ted Allen [EMAIL PROTECTED] To: Sidar López Cruz [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org, [EMAIL PROTECTED] Subject: Re: [PERFORM] Very slow queries Date: Wed, 31 Jan 2007 09:32:43 -0500 How many rows were delete last time you ran the query? Chad's query looks good but here is another variation that may help. Delete From ceroriesgo.salarios Where numero_patrono In (Select ceroriesgo.salarios.numero_patrono From ceroriesgo.salarios Left Join ceroriesgo.patronos Using (numero_patrono) Where ceroriesgo.patronos.numero_patrono Is Null) Executing these query take: Query returned successfully: 290 rows affected, 2542387 ms execution time. I think that's too many time Hope that Helps, Ted Sidar López Cruz wrote: From: Chad Wagner [EMAIL PROTECTED] To: Sidar López Cruz [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very slow queries Date: Tue, 30 Jan 2007 17:37:17 -0500 On 1/30/07, Sidar López Cruz [EMAIL PROTECTED] wrote: query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Select numero_patrono From ceroriesgo.patronos) Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077 width=6) Filter: (NOT (subplan)) SubPlan - Materialize (cost=51021.78..69422.58 rows=1032980 width=25) - Seq Scan on patronos (cost=0.00..41917.80 rows=1032980 width=25) How many rows exist in salarios, but not in patronos? How many rows are there in salarios? Rows: Patronos: 1032980 Salarios: 28480200 What does the explain look like for: delete from ceroriesgo.salarios s where not exists (select 1 from ceroriesgo.patronos where numero_patrono = s.numero_patrono); Also, is this not a case for a foreign key with a cascade delete? No, this is not cascade delete case because I need to delete from salarios not from patronos. http://www.postgresql.org/docs/8.2/static/ddl-constraints.html -- Chad http://www.postgresqlforums.com/ _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend -- *Edward Allen* Software Engineer Black Duck Software, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] T +1.781.891.5100 x133 F +1.781.891.5145 http://www.blackducksoftware.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq _ MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Very slow queries
From: Ted Allen [EMAIL PROTECTED] To: Sidar López Cruz [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very slow queries Date: Tue, 30 Jan 2007 16:14:38 -0500 What indexes do those tables have? Any? Yes: TABLE ceroriesgo.patronos ADD CONSTRAINT patronos_pkey PRIMARY KEY(numero_patrono); INDEX salarios_numero_patrono_idx ON ceroriesgo.salarios USING btree (numero_patrono); Sidar López Cruz wrote: Check this: query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Select numero_patrono From ceroriesgo.patronos) Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077 width=6) Filter: (NOT (subplan)) SubPlan - Materialize (cost=51021.78..69422.58 rows=1032980 width=25) - Seq Scan on patronos (cost=0.00..41917.80 rows=1032980 width=25) These query took a day to finish, how or who can improove better performance of my PostgreSQL. _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- *Edward Allen* Software Engineer Black Duck Software, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] T +1.781.891.5100 x133 F +1.781.891.5145 http://www.blackducksoftware.com _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] how postgresql request the computer resources
Is there something that tells postgres to take the resorces from computer (RAM, HDD, SWAP on linux) as it need, not modifying variables on postgresql.conf and other operating system things? A days ago i am trying to show that postgres is better than mssql but when execute a simple query like: (1) select count(*) from ( select archivo from fotos except select archivo from archivos ) x; Aggregate (cost=182162.83..182162.84 rows=1 width=0) (actual time=133974.495..133974.498 rows=1 loops=1) - Subquery Scan x (cost=173857.98..181830.63 rows=132878 width=0) (actual time=109148.158..15.279 rows=169672 loops=1) - SetOp Except (cost=173857.98..180501.86 rows=132878 width=58) (actual time=109148.144..132094.382 rows=169672 loops=1) - Sort (cost=173857.98..177179.92 rows=1328775 width=58) (actual time=109147.656..113870.975 rows=1328775 loops=1) Sort Key: archivo - Append (cost=0.00..38710.50 rows=1328775 width=58) (actual time=27.062..29891.075 rows=1328775 loops=1) - Subquery Scan *SELECT* 1 (cost=0.00..17515.62 rows=523431 width=58) (actual time=27.052..9560.719 rows=523431 loops=1) - Seq Scan on fotos (cost=0.00..12281.31 rows=523431 width=58) (actual time=27.038..5390.238 rows=523431 loops=1) - Subquery Scan *SELECT* 2 (cost=0.00..21194.88 rows=805344 width=58) (actual time=10.803..12117.788 rows=805344 loops=1) - Seq Scan on archivos (cost=0.00..13141.44 rows=805344 width=58) (actual time=10.784..5420.164 rows=805344 loops=1) Total runtime: 134552.325 ms (2) select count(*) from fotos where archivo not in (select archivo from archivos) Aggregate (cost=29398.98..29398.99 rows=1 width=0) (actual time=26660.565..26660.569 rows=1 loops=1) - Seq Scan on fotos (cost=15154.80..28744.69 rows=261716 width=0) (actual time=13930.060..25859.340 rows=169799 loops=1) Filter: (NOT (hashed subplan)) SubPlan - Seq Scan on archivos (cost=0.00..13141.44 rows=805344 width=58) (actual time=0.319..5647.043 rows=805344 loops=1) Total runtime: 26747.236 ms (3) select count(1) from fotos f where not exists (select a.archivo from archivos a where a.archivo=f.archivo) Aggregate (cost=1761354.08..1761354.09 rows=1 width=0) (actual time=89765.384..89765.387 rows=1 loops=1) - Seq Scan on fotos f (cost=0.00..1760699.79 rows=261716 width=0) (actual time=75.556..0.234 rows=169799 loops=1) Filter: (NOT (subplan)) SubPlan - Index Scan using archivos_archivo_idx on archivos a (cost=0.00..13451.40 rows=4027 width=58) (actual time=0.147..0.147 rows=1 loops=523431) Index Cond: ((archivo)::text = ($0)::text) Total runtime: 89765.714 ms (4) SELECT count(*) FROM fotos f LEFT JOIN archivos a USING(archivo) WHERE a.archivo IS NULL Aggregate (cost=31798758.40..31798758.41 rows=1 width=0) (actual time=114267.337..114267.341 rows=1 loops=1) - Merge Left Join (cost=154143.73..31772412.02 rows=10538550 width=0) (actual time=85472.696..113392.399 rows=169799 loops=1) Merge Cond: (outer.?column2? = inner.?column2?) Filter: (inner.archivo IS NULL) - Sort (cost=62001.08..63309.66 rows=523431 width=58) (actual time=38018.343..39998.201 rows=523431 loops=1) Sort Key: (f.archivo)::text - Seq Scan on fotos f (cost=0.00..12281.31 rows=523431 width=58) (actual time=0.158..4904.410 rows=523431 loops=1) - Sort (cost=92142.65..94156.01 rows=805344 width=58) (actual time=47453.790..50811.216 rows=805701 loops=1) Sort Key: (a.archivo)::text - Seq Scan on archivos a (cost=0.00..13141.44 rows=805344 width=58) (actual time=0.206..7160.148 rows=805344 loops=1) Total runtime: 114893.116 ms WITH ANY OF THIS QUERIES MSSQL TAKES NOT MUCH OF 7 SECONDS PLEASE HELP ME _ Consigue aquí las mejores y mas recientes ofertas de trabajo en América Latina y USA: http://latam.msn.com/empleos/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] performance on query
I DON'T KNOW WHAT TO DO WITH THIS QUERYS... Comparation with sql server, sql server wins !!! Table sizes: archivos: 40MB fotos: 55MB select count(1) from fotos f where not exists (select a.archivo from archivos a where a.archivo=f.archivo) 173713 ms. 110217 ms. 83122 ms. select count(*) from ( select archivo from fotos except select archivo from archivos ) x; 201479 ms. SELECT count(*) FROM fotos f LEFT JOIN archivos a USING(archivo) WHERE a.archivo IS NULL 199523 ms. _ MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] blue prints please
where can i find bests practices for tunning postgresql? _ Consigue aquí las mejores y mas recientes ofertas de trabajo en América Latina y USA: http://latam.msn.com/empleos/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] zero performance on query
what happend with postgresql 8.1b4 performance on query? please help me !!! look at this: select count(*) from fotos where archivo not in (select archivo from archivos) Aggregate (cost=4899037992.36..4899037992.37 rows=1 width=0) - Seq Scan on fotos (cost=22598.78..4899037338.07 rows=261716 width=0) Filter: (NOT (subplan)) SubPlan - Materialize (cost=22598.78..39304.22 rows=805344 width=58) - Seq Scan on archivos (cost=0.00..13141.44 rows=805344 width=58) I WILL DIE WAITING FOR QUERY RESPONSE !!! -- CREATE TABLE archivos ( archivo varchar(20)) WITHOUT OIDS; CREATE INDEX archivos_archivo_idx ON archivos USING btree(archivo); ~80 rows -- CREATE TABLE fotos ( cedula varchar(20), nombre varchar(100), apellido1 varchar(100), apellido2 varchar(100), archivo varchar(20) ) WITHOUT OIDS; CREATE INDEX fotos_archivo_idx ON fotos USING btree (archivo); CREATE INDEX fotos_cedula_idx ON fotos USING btree (cedula); ~50 rows _ Consigue aquí las mejores y mas recientes ofertas de trabajo en América Latina y USA: http://latam.msn.com/empleos/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
