X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 880F4D1BB7D for ; Thu, 21 Aug 2003 17:57:38 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 46922-03 for ; Thu, 21 Aug 2003 17:57:09 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id D4E43D1B9BC for ; Thu, 21 Aug 2003 17:57:06 -0300 (ADT) Received: from sarek.skynet.be (sarek.skynet.be [195.238.3.230]) by www.postgresql.com (Postfix) with ESMTP id 23E6ACF4C54 for ; Thu, 21 Aug 2003 17:57:06 -0300 (ADT) Received: from pleyel.kotnet.org (127.78-200-80.adsl.skynet.be [80.200.78.127]) by sarek.skynet.be (8.12.9/8.12.9/Skynet-OUT-2.21) with ESMTP id h7LKurC9001908; Thu, 21 Aug 2003 22:56:54 +0200 (envelope-from ) Received: from slicks by pleyel.kotnet.org with local (Exim 3.36 #1 (Debian)) id 19pwUN-0007zg-00; Thu, 21 Aug 2003 22:56:59 +0200 Date: Thu, 21 Aug 2003 22:56:59 +0200 From: Mathieu De Zutter To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Sorting a query on a view ignores an index Message-ID: <20030821205659.GA28674@pleyel.kotnet.org> Reply-To: Mathieu De Zutter References: <20030821133538.GA973@pleyel.kotnet.org> <8509.1061477951@sss.pgh.pa.us> <20030821162448.GC973@pleyel.kotnet.org> <14117.1061485328@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <14117.1061485328@sss.pgh.pa.us> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/310 X-Sequence-Number: 3126 On Thu, Aug 21, 2003 at 01:02:08PM -0400, Tom Lane wrote: > Mathieu De Zutter writes: > > Apart from avoiding views or subselects when sorting afterwards and > > putting the whole bunch in a huge SQL statement (which i'll have to > > produce on-the-fly), do you have an other alternative? > > See if you can avoid the subselects in the view's SELECT list. That's > what's preventing 7.3 from doing a good job. First of all, I've installed 7.4beta1 and the performance is as should be expected. Good, I'll try to convince the admin to upgrade to 7.4 when it gets released (though that machine I will eventually have to use, runs debian/stable atm, with pgsql 7.2). Second, I have tried to eliminate the subselects by (left) joining some tables (I only kept a part of the original view to do this(1)). That made me also add a GROUP BY clause to remove duplicates. Comparing to the result-equivalent subselect-version, it has an enormous performance hit (1) I only kept one subselect and rearranged it to left join's Limit (cost=2169.31..2169.36 rows=20 width=140) (actual time=526.12..526.14 rows=20 loops=1) -> Sort (cost=2169.31..2185.79 rows=6591 width=140) (actual time=526.11..526.12 rows=20 loops=1) Sort Key: search_title -> Subquery Scan search_song (cost=1254.25..1484.93 rows=6591 width=140) (actual time=149.29..423.16 rows=6380 loops=1) -> GroupAggregate (cost=1254.25..1419.02 rows=6591 width=49) (actual time=149.28..407.04 rows=6380 loops=1) -> Sort (cost=1254.25..1270.73 rows=6591 width=49) (actual time=147.92..150.75 rows=6380 loops=1) Sort Key: s.id, s.title, s."year" etc... Running half a second. It looks like the grouping is messing it up. No index seems to be used either? (s.id is primary key, s.title = search_title, with index search_title_idx) While the subselect-version shows no sort in its plan and runs in 2msec and shows "Index Scan using song_title_idx on song s" (both queries are sorted on search_title) I'm fairly sure the two queries were written as they should. If you want more info on how I got this result, just tell me. I think my mail is yet long enough. Oh, important detail, these results are obtained with 7.4beta1, I haven't checked it in 7.3 With kind regards, Mathieu