Re: A slow query - Help please?
От | Alban Hertroys |
---|---|
Тема | Re: A slow query - Help please? |
Дата | |
Msg-id | 449691C5.3060100@magproductions.nl обсуждение исходный текст |
Ответ на | Re: A slow query - Help please? ("hubert depesz lubaczewski" <depesz@gmail.com>) |
Ответы |
Re: A slow query - Help please?
(Jim Nasby <jnasby@pervasive.com>)
Re: A slow query - Help please? ("hubert depesz lubaczewski" <depesz@gmail.com>) |
Список | pgsql-general |
hubert depesz lubaczewski wrote: > in case you can't, do something similar to this: > > select * from > ( > select * from only table_a order by number desc limit 25 > union > select * from only table_b order by number desc limit 25 > union > select * from only table_c order by number desc limit 25 > ) x > order by number desc limit 25; > > it should be faster. and yes, i know it's ugly. I found a way that works, and is indeed quite a bit faster. It is even uglier than what you proposed. The problem wasn't the "order by" in the subquery, but the "order by" combined with the "union": EXPLAIN ANALYZE SELECT * FROM ( SELECT number, otype, owner, snumber, dnumber, rnumber, dir, pos FROM ONLY mm_posrel ORDER BY number DESC LIMIT 25 ) a UNION ALL SELECT * FROM ( SELECT number, otype, owner, snumber, dnumber, rnumber, dir, pos FROM ONLY mm_menu_item ORDER BY number DESC LIMIT 25 ) b UNION ALL SELECT * FROM ( SELECT number, otype, owner, snumber, dnumber, rnumber, dir, pos FROM ONLY mm_cms_operation ORDER BY number DESC LIMIT 25 ) c ORDER BY number DESC LIMIT 25; Output of explain is attached, for those interested. Now all we need to do is getting MMBase to do its queries like this :P Thanks a bunch for setting me on the right track. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=7.65..7.71 rows=25 width=60) (actual time=1.352..1.678 rows=25 loops=1) -> Sort (cost=7.65..7.84 rows=75 width=60) (actual time=1.344..1.428 rows=25 loops=1) Sort Key: number -> Append (cost=0.00..5.32 rows=75 width=60) (actual time=0.069..1.164 rows=25 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..1.72 rows=25 width=60) (actual time=0.063..0.877 rows=25 loops=1) -> Subquery Scan a (cost=0.00..1.47 rows=25 width=60) (actual time=0.052..0.651 rows=25 loops=1) -> Limit (cost=0.00..1.22 rows=25 width=60) (actual time=0.040..0.386 rows=25 loops=1) -> Index Scan Backward using mm_posrel_pkey on mm_posrel (cost=0.00..3675.54 rows=75595width=60) (actual time=0.032..0.187 rows=25 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..1.80 rows=25 width=60) (actual time=0.031..0.031 rows=0 loops=1) -> Subquery Scan b (cost=0.00..1.55 rows=25 width=60) (actual time=0.023..0.023 rows=0 loops=1) -> Limit (cost=0.00..1.30 rows=25 width=60) (actual time=0.017..0.017 rows=0 loops=1) -> Index Scan Backward using mm_menu_item_pkey on mm_menu_item (cost=0.00..52.00 rows=1000width=60) (actual time=0.009..0.009 rows=0 loops=1) -> Subquery Scan "*SELECT* 3" (cost=0.00..1.80 rows=25 width=60) (actual time=0.028..0.028 rows=0 loops=1) -> Subquery Scan c (cost=0.00..1.55 rows=25 width=60) (actual time=0.020..0.020 rows=0 loops=1) -> Limit (cost=0.00..1.30 rows=25 width=60) (actual time=0.013..0.013 rows=0 loops=1) -> Index Scan Backward using mm_cms_operation_pkey on mm_cms_operation (cost=0.00..52.00rows=1000 width=60) (actual time=0.007..0.007 rows=0 loops=1) Total runtime: 2.077 ms (17 rows)
В списке pgsql-general по дате отправления: