Re: Sorting a query on a view ignores an index - Mailing list pgsql-performance

From
Subject Re: Sorting a query on a view ignores an index
Date
Msg-id 001201c36895$bc9a9910$1401010a@Xeon
Whole thread Raw
In response to Sorting a query on a view ignores an index  (Mathieu De Zutter <mathieu@dezutter.org>)
List pgsql-performance
Hello,

Here is my exp. regarding VIEW (create VIEW view_name AS SELECT ....)
optimization.
I have found strange (or at least not very intelligent) behavior of query
planner (v.7.3.4).
Given task is to take data from 14 tables, join, group, sort, etc. I
currently use about ten views referencing each other.

I have done a lot of job to adjust 'postgresql.conf' configuration.
Except common increase of shared_mem&buffer stuff I felt I was unable to do
anything else.
So, initialy, time to generate the Monthly report was about 10 sec.
I started to play with GEQO....
Screaming success -- I downplayed query execution time about 3x JUST by
setting geqo_threshold=3.
Now my Monthly report takes about 3 sec.
(Users are running that sequence of select queries (I call it invocation of
the final view ;-) quite often  for any given period of time --
 usually from one week to half a year (rarely) so it is essential part of my
dbvs.

Maybe my exp. will encourage somebody to play with scary genetic stuff. ;-)

Good luck & Best Regards,
Alvis


pgsql-performance by date:

Previous
From: Mathieu De Zutter
Date:
Subject: Re: Sorting a query on a view ignores an index
Next
From: Robert Treat
Date:
Subject: Re: Tests