Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dániel Dénes <panther-d@freemail.hu> writes:
> > But even then, it won't realize that the result are in correct
> > order, and does a sort! Why?
>
> In general the output of a nestloop doesn't derive any ordering
> properties from the inner scan. It might happen to work in your
> particular case because on the outer side (site_id, order) is unique
> and so the "order" values must be strictly increasing. But if there
> could be multiple rows with the same "order" value coming from the
> outer side, then it would be incorrect to claim that the join output is
> sorted by (outer.order, inner.order).
>
> It's possible that the planner could be taught to recognize this
> situation, but it looks to me like doing that would result in drastic
> increases in planning time for many queries (due to having to consider
> a lot more Paths) with a resulting win in only a very few.
>
> regards, tom lane
When you wrote this answer, I thought maybe it's really a one-time
problem, and it's not worth spending much time on it, because the
tables involved had 10-100 rows, so a sort wasn't really that scary; I
just wanted to know the cause.
But now I ran into this again. There are 2 tables involved (simplified):
banners_places:
- id integer (PKEY)
- pageid integer (FKEY to a table not involved now)
- place text
UNIQUE KEY: (pageid, place)
banners_show:
- id integer (PKEY)
- bplid integer (FKEY to banners_places.id)
- uptime timestamp
INDEX: (bplid, uptime)
My query is:
SELECT *
FROM banners_places AS bpl
JOIN banners_show AS bsh ON bsh.bplid = bpl.id
WHERE bpl.pageid = 123
ORDER BY bpl.place, bsh.uptime
To me it looks like the best plan would be to get the desired rows from
banners_places and then do a NestLoop join using the index on
banners_show. This way no sorting should be necessary.
But even though I forced PG to do my plan (disabled almost every
alternative), the sort is there:
Sort
Sort Key: bpl.place, bsh.uptime
-> Nested Loop
-> Index Scan using bpl_UNIQUE on banners_places bpl
Index Cond: (pageid = 123)
-> Index Scan using bsh_INDEX on banners_show bsh
Index Cond: (bsh.bplid = "outer".id)
Are you sure this can't be fixed without drastically increasing planning
time?
Or is there a way I can make this query not to do a sort?
Regards,
Denes Daniel
___________________________________________________________
Légy mindig trendi és naprakész - olvass magazinokat a mobilodon Mobizinnel!
www.t-mobile.hu/mobizin