On Thu, Aug 21, 2003 at 01:02:08PM -0400, Tom Lane wrote:
> Mathieu De Zutter <mathieu@dezutter.org> 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
