Re: Speed of postgres compared to ms sql, is this

2006/12/5, Tom Lane <tgl@sss.pgh.pa.us>:

> These sorts of reports would be far more helpful if they contained some
> specifics.  What queries does MSSQL do better than Postgres, exactly?

You are of course correct, Tom.
I'm sorry I'm not in a position to replay what I've been doing a year
ago...I wish I could.

Obviously, I never had to worry about the concepts of vacuuming and
analysis (not that it's very difficult with pgsql: it just doesn't
exist as a concept with MSSQL).
Anyone calling my comment completely subjective would be completely
correct because that's what it was.

One type of query does come to mind, now that I think about it.
pgsql has trouble handling queries like
SELECT * FROM t0 WHERE t0.id_t1 IN (SELECT t1.id FROM t1 WHERE...)

The performance is a bit better when there's only one result in the
subselect so you can do:
SELECT * FROM t0 WHERE t0.id_t1 = (SELECT t1.id FROM t1 WHERE...)

When the subselect returns a lot of results, pgsql really takes it's time.

The first query, however, can be executed much, much (at least an
order of magnitude) quicker like this:
SELECT * FROM t0
LEFT OUTER JOIN t1 ON t1.id = t0.id_t1
WHERE t1.id IS NOT NULL

I didn't notice this kind of sensitivity with MSSQL, but again, I
can't easily reproduce what I've been doing.

Sorry for the original FUD-like report. Cheers,
t.n.a.