From: Jim C. Nasby <jnasby@pervasive.com>
To: Antoine <melser.anton@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query stopped working after tables > 50000 records
Date: Fri, 20 Jan 2006 13:04:37 -0600
Message-ID: <20060120190437.GN20182@pervasive.com> (raw)
In-Reply-To: <43D12CC2.8050408@gmail.com>
References: <43D12CC2.8050408@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, melser.anton@gmail.com
Subject: Re: query stopped working after tables > 50000 records
In-Reply-To: <20060120190437.GN20182@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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