Re: A slow query - Help please? - Mailing list 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)