Slow SELECT

[Frank Millman]

Hi all

I have a SELECT that runs over 5 times slower on PostgreSQL compared 
with Sql Server and sqlite3. I am trying to understand why.

I have a table that looks like this (simplified) -

CREATE TABLE my_table (
    row_id SERIAL PRIMARY KEY,
    deleted_id INT DEFAULT 0,
    fld_1 INT REFERENCES table_1(row_id),
    fld_2 INT REFERENCES table_2(row_id),
    fld_3 INT REFERENCES table_3(row_id),
    fld_4 INT REFERENCES table_4(row_id),
    tran_date DATE,
    tran_total DEC(21,2)
    );

CREATE UNIQUE INDEX my_table_ndx ON my_table (fld_1, fld_2, fld_3, 
fld_4, tran_date) WHERE deleted_id = 0;

The table sizes are -
    my_table : 167 rows
    table_1 : 21 rows
    table_2 : 11 rows
    table_3 : 3 rows
    table_4 : 16 rows

Therefore for each tran_date in my_table there are potentially 
21x11x3x16 = 11088 rows. Most will be null.

I want to select the row_id for the last tran_date for each of those 
potential groups. This is my select -

    SELECT (
        SELECT a.row_id FROM my_table a
        WHERE a.fld_1 = b.row_id
        AND a.fld_2 = c.row_id
        AND a.fld_3 = d.row_id
        AND a.fld_4 = e.row_id
        AND a.deleted_id = 0
        ORDER BY a.tran_date DESC LIMIT 1
    )
    FROM table_1 b, table_2 c, table_3 d, table_4 e

Out of 11088 rows selected, 103 are not null.

On identical data, this takes 0.06 sec on SQL Server, 0.04 sec on 
sqlite3, and 0.31 sec on PostgreSQL.

I have looked at the EXPLAIN, but I don't really know what to look for. 
I can supply it if that would help.

Thanks for any advice.

Frank Millman