Richard Neill <rn214@cam.ac.uk> wrote:
> Am I wrong in thinking that ORDER BY is always applied after the
> main query is run?
Yes, you are wrong to think that. It compares the costs of various
plans, and when it has an index with the high order portion matching
your ORDER BY clause, it may think that it can scan that index to
generate the correct sequence. If the sort is estimated to be
expensive enough compared to the index scan, it will use the index
scan and skip the sort. Sorting hundreds of billions of rows can be
expensive.
> Even if I run it this way:
>
> select * from (select * from h.inventory where demand_id
> =289276563) as sqry order by id;
>
> which should(?) surely force it to run the first select, then
> sort,
I wouldn't necessarily assume that. You can EXPLAIN that form of
the query and find out easily enough. Does it say:
-> Index Scan using inventory_demand_id on
inventory (cost=0.00..22.36 rows=28 width=56) (actual time=0.025..0.053
rows=15 loops=1)
Index Cond: (demand_id = 289276563)
or:
-> Index Scan using inventory_pkey on
inventory (cost=0.00..879728.20 rows=28 width=56) (actual
time=31738.956..32860.738 rows=15 loops=1)
Filter: (demand_id = 289276563)
> it's quick if I do order by id+1
You don't have an index on id+1.
> The table definitions are as follows (sorry there are so many).
I'll poke around to try to get a clue why the estimated result rows
are so far off, but I may be in over my head there, so hopefully
others will look, too. For one thing, I haven't used inheritance,
and I don't know how that might be playing into the bad estimates.
(At first glance, it does seem to get into trouble about the time it
estimates the rows for the outer joins to those.)
The real problem to solve here is that it's estimating the rows
count for the result so badly. If you need a short-term
work-around, you've already discovered that you can keep it from
using the index on id for ordering by creating an expression using
id which causes it not to consider the index a match. That's kind
of ugly to keep long term, though.
-Kevin