Skip site navigation (1) Skip section navigation (2)

Re: Question about difference in performance of 2 queries

From: Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org>
To: Sean Shanny <shannyconsulting(at)earthlink(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Question about difference in performance of 2 queries
Date: 2003-12-29 16:48:49
Message-ID: Pine.LNX.4.44.0312291740510.13889-100000@zigo.dhs.org (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Sean ShannyDate: 2003-12-29 17:44:11
Subject: Re: Question about difference in performance of 2 queries
Previous:From: Tom LaneDate: 2003-12-29 16:48:07
Subject: Re: Question about difference in performance of 2 queries on large table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group