[Info-Ingres] To order by, or NOT to order by

[Laframboise André]

I'd like to do that (edit stats) but I have to be careful not to affect
other queries/QEPs on this table.

As for creating new secondary indexes as mentioned earlier, that may help 
For this query but there are many 'similar' queries and it would be difficult 
Creating a single secondary to satisfy all variants.

If I edited the stats, would just decreasing the rows make a difference ?

A last dicth solution may be to open up the code and add a 'set trace point op165'
ahead of all these queries. But If I add a patch or upgrade later, it would be a 
whole new ballgame.

Andre



-----Original Message-----
From: info-ingres-bounces at kettleriverconsulting.com [mailto:info-ingres-bounces at kettleriverconsulting.com] On Behalf Of Roy Hann
Sent: Tuesday, December 11, 2007 12:08 PM
To: info-ingres at kettleriverconsulting.com
Subject: Re: [Info-Ingres] To order by, or NOT to order by

"Laframboise André" <andre.laframboise at lac-bac.gc.ca> wrote in message news:mailman.52.1197388637.2388.info-ingres at kettleriverconsulting.com...
>
> For all but the big tables, just the basic -zk flag.
>
> For all tables over 10 million rows, I started with relatively small 
> samples, but then dropped the -zs flag altogether when there were no 
> changes in the QEP.
>
> But I never used the (-zn?) to read all the rows. I figured it would 
> take hours and hours.
> But maybe I have no choice.
>
> i also tried importing the stats from the old version of the database 
> where the optimizer generated the best QEP but I guess the optimizer 
> in 2.6/604 is a bit different.

There is no reason why you shouldn't just make up some stats if you
(somehow) know what the data look like.  In fact there is no particular harm in lying about it if it seduces the optimizer into doing what you want.

Just dump the real stats to a file with statdump, edit them to your liking, then reload them with optimizedb -i fname.

Roy