Hi Akshita
On Sun, Mar 15, 2015 at 12:10:28PM +0530, Akshita Jha wrote:
> On Thu, Mar 12, 2015 at 8:30 PM, Andreas Tille <andreas@an3as.eu> wrote:
> > I think this would be also acceptable to run on production UDD.
> > However, I wonder whether you could do some further review after this
> > issue should be clarified. Perhaps we get another factor 2 or 4 for
> > speeding up to make sure we will not uselessly stress test Debian
> > hardware.
>
> I have been working to improve the performance of "query_bug_packages".
> I tried using array_agg(DISTINCT ....)[1] instead of "PARTITION BY() then
> selecting row_number()=1". Also, I tried selecting the MAX() and MIN()
> after using "ORDER BY s.version and bdp.property" respectively in the two
> sub-queries. However, the timing after using both the above, increased as
> the issue here was that we needed to use GROUP BY ,and GROUP BY is really
> expensive.
>
> I also tried converting 'version' to binary by using 'strip_binary_upload'
> but here also the timing increased slightly.
>
> Do you have any other ideas in mind that I can experiment with?
I think your experiments are reasonable. I can not come up with more
suggestions. If you were able to get better results specifically for
the larger queries in debian-edu feel free to commit them. I guess for
even more fine tuning we would throw the question to some postgresql
related lists where some experts might hang around (I do not consider
myself an postgres expert ;-)).