pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedToo 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