pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery stopped working after tables > 50000 records
2+ messages / 2 participants
[nested] [flat]
* query stopped working after tables > 50000 records
@ 2006-01-20 18:32 Antoine <melser.anton@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Antoine @ 2006-01-20 18:32 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a query that does a left outer join. The query gets some text
from a reference table where one of the query's main tables may or may
not have the text's tables id. It wasn't super fast, but now it simply
won't execute. It won't complete either through odbc or via pgadmin
(haven't yet tried via psql). A week ago (with considerably fewer
records in the main table) it executed fine, not particularly quickly,
but not that slowly either. Now it locks up postgres completely (if
nothing else needs anything it takes 100% cpu), and even after an hour
gives me nothing. I have come up with a solution that gets the text via
another query (possibly even a better solution), but this seems very
strange.
Can anyone shed some light on the subject? I tried a full vacuum on the
tables that needed it, and a postgres restart, all to no avail.
Cheers
Antoine
ps. I can send the query if that will help...
pps. running a home-compiled 8.1.1 with tables in the query having 70000
records, 30000 records and 10 for the outer join. Without the left outer
join it runs in ~ 1 second.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: query stopped working after tables > 50000 records
@ 2006-01-20 19:04 Jim C. Nasby <jnasby@pervasive.com>
parent: Antoine <melser.anton@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jim C. Nasby @ 2006-01-20 19:04 UTC (permalink / raw)
To: Antoine <melser.anton@gmail.com>; +Cc: pgsql-performance
Send query, output of EXPLAIN and table definitions.
On Fri, Jan 20, 2006 at 07:32:34PM +0100, Antoine wrote:
> Hi,
> I have a query that does a left outer join. The query gets some text
> from a reference table where one of the query's main tables may or may
> not have the text's tables id. It wasn't super fast, but now it simply
> won't execute. It won't complete either through odbc or via pgadmin
> (haven't yet tried via psql). A week ago (with considerably fewer
> records in the main table) it executed fine, not particularly quickly,
> but not that slowly either. Now it locks up postgres completely (if
> nothing else needs anything it takes 100% cpu), and even after an hour
> gives me nothing. I have come up with a solution that gets the text via
> another query (possibly even a better solution), but this seems very
> strange.
> Can anyone shed some light on the subject? I tried a full vacuum on the
> tables that needed it, and a postgres restart, all to no avail.
> Cheers
> Antoine
> ps. I can send the query if that will help...
> pps. running a home-compiled 8.1.1 with tables in the query having 70000
> records, 30000 records and 10 for the outer join. Without the left outer
> join it runs in ~ 1 second.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-20 19:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-20 18:32 query stopped working after tables > 50000 records Antoine <melser.anton@gmail.com>
2006-01-20 19:04 ` Jim C. Nasby <jnasby@pervasive.com>
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