Re: [PERFORM] order by index, and inheritance
Rod Taylor <[EMAIL PROTECTED]> wrote: > The scan is picking the best method for grabbing everything within the > table, since it is not aware that we do not require everything. Hmm. That is a bit silly. Why does it use the index if select only from mm_mediasources? > You can explicitly tell it what you want to do via: > > SELECT * > FROM (SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20 > UNION SELECT * FROM ORDER BY number DESC LIMIT 20) AS tab > ORDER BY number DESC LIMIT 20 I think you meant 'only mm_mediasources', and btw order by and limit are not accepted before union, so the above query does not compile. I can't figure out any acceptable work-around. Even if something as the above would work, it still would be hardly elegant, and you can as well have no support for inheritance (actually, you can _better_ have no inheritance, because at least it is clear what works then). Michiel btw. Why are these messages not appearing on the list itself? -- Michiel Meeuwissen Mediapark C101 Hilversum +31 (0)35 6772979 nl_NL eo_XX en_US mihxil' [] () ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] order by index, and inheritance
Rod Taylor <[EMAIL PROTECTED]> wrote: > On Thu, 2004-04-22 at 07:02, Michiel Meeuwissen wrote: > > Rod Taylor <[EMAIL PROTECTED]> wrote: > > > The scan is picking the best method for grabbing everything within the > > > table, since it is not aware that we do not require everything. > > > > Hmm. That is a bit silly. Why does it use the index if select only from > > mm_mediasources? > > > > > You can explicitly tell it what you want to do via: > > > > > > SELECT * > > > FROM (SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20 > > > UNION SELECT * FROM ORDER BY number DESC LIMIT 20) AS tab > > > ORDER BY number DESC LIMIT 20 > > > > I think you meant 'only mm_mediasources', and btw order by and limit are not > > accepted before union, so the above query does not compile. > > Yes, I did mean only. Try putting another set of brackets around the > selects to get ORDER BY, etc. accepted. You can add another layer of > subselects in the from if that doesn't work. Ok, I can get it working: select number,url from ( select number,url from (select number,url from only mm_mediasources order by number desc limit 20) as A union select number,url from (select number,url from mm_audiosources order by number desc limit 20) as B union select number,url from (select number,url from mm_videosources order by number desc limit 20) as C ) as TAB order by number desc limit 20; This indeeds performs good (about 1 times faster then select number,url from mm_mediasources order by number desc limit 20) . But hardly beautiful, and quite useless too because of course I am now going to want to use an offset (limit 20 offset 20, you see..), which seems more or less impossible in this way, isn't it. select number,url from ( select number,url from (select number,url from only mm_mediasources order by number desc limit 100020) as A union select number,url from (select number,url from mm_audiosources order by number desc limit 100020) as B union select number,url from (select number,url from mm_videosources order by number desc limit 100020) as C ) as TAB order by number desc limit 20 offset 10; This would be it, I think, but this performs, expectedly, quit bad again, though still 5 times faster then select url,number from mm_mediasources order by number desc limit 20 offset 10; I'm thinking of dropping inheritance all together and using foreign keys or so for the extra fields, to simulate inheritance. That might perhaps work a whole lot better? Thanks anyway, -- Michiel Meeuwissen Mediapark C101 Hilversum +31 (0)35 6772979 nl_NL eo_XX en_US mihxil' [] () ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] order by index, and inheritance
> This indeeds performs good (about 1 times faster then select number,url > from mm_mediasources order by number desc limit 20) . But hardly beautiful, > and quite useless too because of course I am now going to want to use an > offset (limit 20 offset 20, you see..), which seems more or less impossible > in this way, isn't it. Yes, and the offset is a good reason why PostgreSQL will not be able to do it by itself either. Is "number" unique across the board? If so, instead of the offset you could use WHERE number > $lastValue. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] order by index, and inheritance
On Thu, 2004-04-22 at 07:02, Michiel Meeuwissen wrote: > Rod Taylor <[EMAIL PROTECTED]> wrote: > > The scan is picking the best method for grabbing everything within the > > table, since it is not aware that we do not require everything. > > Hmm. That is a bit silly. Why does it use the index if select only from > mm_mediasources? > > > You can explicitly tell it what you want to do via: > > > > SELECT * > > FROM (SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20 > > UNION SELECT * FROM ORDER BY number DESC LIMIT 20) AS tab > > ORDER BY number DESC LIMIT 20 > > I think you meant 'only mm_mediasources', and btw order by and limit are not > accepted before union, so the above query does not compile. Yes, I did mean only. Try putting another set of brackets around the selects to get ORDER BY, etc. accepted. You can add another layer of subselects in the from if that doesn't work. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] order by index, and inheritance
I have a query which performs not so well: SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20; costs nearly a minute. The table contains over 300 000 records. The table has two extensions, which are (a the moment) nearly empty, but have something to do with this, because: SELECT * FROM only mm_mediasources ORDER BY number DESC LIMIT 20; performs ok (8ms). The query plan is then as I would expect: media=# explain SELECT * FROM only mm_mediasources ORDER BY number DESC LIMIT 20; QUERY PLAN - Limit (cost=0.00..8.36 rows=20 width=105) -> Index Scan Backward using mediasource_object on mm_mediasources (cost=0.00..114641.05 rows=274318 width=105) The query plan of the original query, without 'only' does table scans: media=# explain SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20; QUERY PLAN Limit (cost=47248.70..47248.75 rows=20 width=105) -> Sort (cost=47248.70..47934.52 rows=274328 width=105) Sort Key: public.mm_mediasources.number -> Result (cost=0.00..8364.28 rows=274328 width=105) -> Append (cost=0.00..8364.28 rows=274328 width=105) -> Seq Scan on mm_mediasources (cost=0.00..8362.18 rows=274318 width=105) -> Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84) -> Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.09 rows=9 width=89) and presumably because if that performs so lousy. Simply selecting on a number does work fast: media=# explain SELECT * FROM mm_mediasources where number = 606973 ; QUERY PLAN Result (cost=0.00..6.13 rows=4 width=105) -> Append (cost=0.00..6.13 rows=4 width=105) -> Index Scan using mediasource_object on mm_mediasources (cost=0.00..4.00 rows=2 width=105) Index Cond: (number = 606973) -> Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84) Filter: (number = 606973) -> Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.11 rows=1 width=89) Filter: (number = 606973) (3ms) I suppose seq scans are used on the extensions because they contain so few records. All tables have index on number. How do I force it to use them also when I use order by? I use psql 7.3.2 Michiel -- Michiel Meeuwissen | Mediapark C101 Hilversum | +31 (0)35 6772979| I hate computers nl_NL eo_XX en_US| mihxil' | [] () | ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
