Skip site navigation (1) Skip section navigation (2)

Re: Speed or configuration

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Franz J Fortuny <ffortuny(at)ivsol(dot)com>
Cc: pgSQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Speed or configuration
Date: 2000-08-20 18:11:58
Message-ID: Pine.BSF.4.10.10008201105590.13545-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
(It won't really be forever, just probably
a really long time)

You can usually get around it by rewriting the
query to use EXISTS rather than IN.

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Sun, 20 Aug 2000, Franz J Fortuny wrote:

> At our company we are presently using a commercial
> database that generates results from this query:
> 
> select xx1,xx2,xx3 from tableX
> where field1 in
> (select field1 from tableY where
> field2=NNN and field3=NNN2 and field4=NNN4)
> 
> tableX has 790,000 rows, and an index on field1
> tableY has abou 175,000 rows and an index that includes
> field2,field3,field4 (and 2 other fields not being used
> here)
> 
> Of course, the order in the indexes is the logical one.
> 
> I have made copies of the tables from the commercial SQL
> server to PostgreSQL and PostgreSQL is consistently
> faster in things like count(*) and certain other
> queries.
> 
> But when it comes to the above mentioned query,
> PostgreSQL simply stays there, forever. The postgres
> backend must be killed in order to free the client
> program.


In response to

pgsql-sql by date

Next:From: The Hermit HackerDate: 2000-08-20 19:27:17
Subject: Re: update syntax error?
Previous:From: Tom LaneDate: 2000-08-20 18:01:30
Subject: Re: Speed or configuration

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group