From: alvis@piladzi-2.biz
To: pgsql-performance@postgresql.org
Subject: Re: Sorting a query on a view ignores an index
Date: Fri, 22 Aug 2003 13:11:19 +0300
Message-ID: <001201c36895$bc9a9910$1401010a@Xeon> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: alvis@piladzi-2.biz
Subject: Re: Sorting a query on a view ignores an index
In-Reply-To: <001201c36895$bc9a9910$1401010a@Xeon>
References: <001201c36895$bc9a9910$1401010a@Xeon>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox