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.
Any suggestions?
Franz J Fortuny