So, a few weeks ago, I asked about Oracle execution plan cost vs speed in relation to the FIRST_ROWS(n) hint. I've run into a similar issue, but this time around the ORDERED hint. When I use the hint, my execution time improves dramatically (upwards of 90%), but the EXPLAIN PLAN for the query reports an enormous cost increase. In this particular query, the cost goes from 1500 to 24000.
The query is paramterized for pagination, and joins 19 tables to get the data out. I'd post it here, but it is 585 lines long and is written for a vendor's messy, godawful schema. Unless you happened to be intimately familiar with the product this is used for, it wouldn't be much help to see it. However, I gathered the schema stats at 100% shortly before starting work on tuning the query, so the CBO is not working in the dark here.
I'll try to summarize what the query does. The query essentially returns objects and their children in the system, and is structured as a large subquery block joined directly to several tables. The first part returns object IDs and is paginated inside its query block, before the joins to other tables. Then, it is joined to several tables that contain child IDs.
I know that the CBO is not all knowing or infalible, but it really bothers me to see an execution plan this costly perform so well; it goes against a lot of what I've been taught. With the FIRST_ROWS hint, the solution was to provide a value n such that the optimizer could reliably generate the execution plan. Is there a similar kind of thing happening with the ORDERED hint for my query?
The reported cost is for the execution of the complete query, not just the first set of rows. (PostgreSQL does the costing slightly differently, in that it provides the cost for the initial return of rows and for the complete set).
For some plans the majority of the cost is incurred prior to returning the first rows (eg where a sort-merge is used), and for others the initial cost is very low but the cost per row is relatively high thereafter (eg. nested loop join).
So if you are optimising for the return of the first few rows and joining 19 tables you may get a very low cost for the return of the first 20 with a nested loop-based plan. However for of the complete set of rows the cost of that plan might be very much higher than others that are optimised for returning all rows at the expense of a delay in returning the first.