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.
>
> Any suggestions?
First question(s) I have is what version of PostgreSQL? what hardware is
the commercial SQL server running on? PostgreSQL? memory, cpu, hard
drives, etc?
I run something similar on a 7.0.2 machine with a table consisting of
~9million rows and another of ~100k, and results come back in <1min ... in
fact, the exact query I've tried looks something like:
SELECT url_id,intag FROM ndictWHERE word_id=-903076670 AND url_id IN ( SELECT distinct url_id FROM
ndict WHERE word_id=-70722333 AND url_id IN ( SELECT distinct url_id
FROM ndict,url WHERE ndict.word_id=572517542
AND url.rec_id=ndict.url_id AND ((url.url || '') LIKE
'http://www.postgresql.org/%')) );
with subsequent run results looking like:
0.000u 0.014s 0:24.12 0.0% 768+1116k 0+0io 0pf+0w
%time psql -h db -o tmpfile udmsearch < query
0.006u 0.006s 0:08.56 0.0% 0+0k 1+0io 0pf+0w
%time psql -h db -o tmpfile udmsearch < query
0.005u 0.000s 0:09.10 0.0% 0+0k 0+0io 0pf+0w
%time psql -h db -o tmpfile udmsearch < query
0.000u 0.012s 0:11.07 0.0% 300+540k 0+0io 0pf+0w
