pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Too slow
5+ messages / 4 participants
[nested] [flat]

* Too slow
@ 2005-03-22 14:23  Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  0 siblings, 2 replies; 5+ messages in thread

From: Sabio - PSQL @ 2005-03-22 14:23 UTC (permalink / raw)
  To: PostgreSQL Admin <pgsql-admin@postgresql.org>

How can I improve speed on my queries. For example this query takes one 
day executing itself and it has not finalized !!!
"create table tmp_partes as select * from partes where identificacion 
not in (select cedula from sujetos)"

partes have 1888000 rows, an index on identificacion
sujetos have 5500000 rows, an index on cedula







^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Too slow
@ 2005-03-22 18:49  Chris Hoover <revoohc@sermonaudio.com>
  parent: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  1 sibling, 1 reply; 5+ messages in thread

From: Chris Hoover @ 2005-03-22 18:49 UTC (permalink / raw)
  To: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>; +Cc: PostgreSQL Admin <pgsql-admin@postgresql.org>

Sabio - PSQL wrote:

> How can I improve speed on my queries. For example this query takes 
> one day executing itself and it has not finalized !!!
> "create table tmp_partes as select * from partes where identificacion 
> not in (select cedula from sujetos)"
>
> partes have 1888000 rows, an index on identificacion
> sujetos have 5500000 rows, an index on cedula
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
try create table tmp_partes as select * from partes where not exists 
(select cedula from sujetos where cedula = partes.identificacion);

The "not in (subselect)" is very slow in postgresql.

HTH,

chris




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [ADMIN] Too slow
@ 2005-03-22 18:50  Thomas F.O'Connell <tfo@sitening.com>
  parent: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  1 sibling, 1 reply; 5+ messages in thread

From: Thomas F.O'Connell @ 2005-03-22 18:50 UTC (permalink / raw)
  To: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>; +Cc: pgsql-performance

Please post the results of that query as run through EXPLAIN ANALYZE.

Also, I'm going to reply to this on pgsql-performance, which is 
probably where it better belongs.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Mar 22, 2005, at 8:23 AM, Sabio - PSQL wrote:

> How can I improve speed on my queries. For example this query takes 
> one day executing itself and it has not finalized !!!
> "create table tmp_partes as select * from partes where identificacion 
> not in (select cedula from sujetos)"
>
> partes have 1888000 rows, an index on identificacion
> sujetos have 5500000 rows, an index on cedula




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Too slow
@ 2005-03-22 19:10  Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  parent: Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Sabio - PSQL @ 2005-03-22 19:10 UTC (permalink / raw)
  To: PostgreSQL Admin <pgsql-admin@postgresql.org>

WITH: select * from partes where cedula not in (select cedula from sujetos)
Seq Scan on partes  (cost=0.00..168063925339.69 rows=953831 width=109)
  Filter: (NOT (subplan))
  SubPlan
    ->  Seq Scan on sujetos  (cost=0.00..162348.43 rows=5540143 width=15)

WITH: select * from partes where not exists (select cedula from sujetos 
where cedula=partes.cedula)
Seq Scan on partes  (cost=0.00..7373076.94 rows=953831 width=109)
  Filter: (NOT (subplan))
  SubPlan
    ->  Index Scan using sujetos_pkey on sujetos  (cost=0.00..3.84 
rows=1 width=15)
          Index Cond: ((cedula)::text = ($0)::text)

Thomas F. O'Connell wrote:

> Please post the results of that query as run through EXPLAIN ANALYZE.
>
> Also, I'm going to reply to this on pgsql-performance, which is 
> probably where it better belongs.
>
> -tfo
>
> -- 
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Mar 22, 2005, at 8:23 AM, Sabio - PSQL wrote:
>
>> How can I improve speed on my queries. For example this query takes 
>> one day executing itself and it has not finalized !!!
>> "create table tmp_partes as select * from partes where identificacion 
>> not in (select cedula from sujetos)"
>>
>> partes have 1888000 rows, an index on identificacion
>> sujetos have 5500000 rows, an index on cedula
>
>
>
>





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Too slow
@ 2005-03-22 19:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Chris Hoover <revoohc@sermonaudio.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2005-03-22 19:14 UTC (permalink / raw)
  To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>; PostgreSQL Admin <pgsql-admin@postgresql.org>

"Chris Hoover" <revoohc@sermonaudio.com> writes:
> The "not in (subselect)" is very slow in postgresql.

It's OK as long as the subselect result is small enough to hash, but
with 5500000 rows that's not going to happen :-(.

Another issue is that if there are any NULLs in the subselect then you
will probably not like the results.  They are correct per spec but not
very intuitive.

Personally I'd try ye olde outer join trick:

select partes.*
  from partes left join sujetos on (identificacion = cedula)
  where cedula is null;

A merge join on this would likely be the most effective solution.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-03-22 19:14 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-22 14:23 Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
2005-03-22 18:49 ` Chris Hoover <revoohc@sermonaudio.com>
2005-03-22 19:14   ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-22 18:50 ` Thomas F.O'Connell <tfo@sitening.com>
2005-03-22 19:10   ` Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox