pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (7+ messages)

Message-ID: <001201c36895$bc9a9910$1401010a@Xeon>
Permalink:  ../001201c36895$bc9a9910$1401010a@Xeon/
Also on:    postgresql.org/message-id/001201c36895$bc9a9910$1401010a@Xeon

 · 

reply

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