Re: Question about difference in performance of 2 queries

On Mon, 29 Dec 2003, Sean Shanny wrote:

> The first plan below has horrendous performance. we only get about 2%
> CPU usage and iostat shows 3-5 MB/sec IO. The second plan runs at 30%
> cpu and 15-30MB.sec IO.
>
> Could someone shed some light on why the huge difference in
> performance? Both are doing index scans plus a filter. We have no
> content_keys below -1 at this time so the queries return the same results.

EXPLAIN ANALYZE gives more information then EXPLAIN, and is prefered.

It uses different indexes in the two queries, and one seems to be
faster then the other. Why, I can't tell yet.

I would assume that you would get the fastet result if you had an index

(content_key, date_key)

I don't know if pg will even use an index to speed up a <> operation. When
you had > then it could use the idx_pageviews_content index. Why it choose
that when the other would be faster I don't know. Maybe explain analyze
will give some hint.

--
/Dennis