planner parameters - Mailing list pgsql-performance

Hi,

depending on the OFFSET parameter I have seen at least 3 different query
plans.

SELECT * FROM
    (
     SELECT * FROM transaction tt
     WHERE
         tt.account_id = '1376641'
         AND tt.transaction_time >= '2013-02-03 05:37:24'
         AND tt.transaction_time < '2013-08-23 05:37:24'
     ORDER BY
         tt.transaction_time ASC,
         tt.id ASC
     LIMIT 10000
     OFFSET 0
    ) t1
    LEFT OUTER JOIN fmb t2
        ON (t1.fmb_id = t2.id)
    LEFT OUTER JOIN payment.payment t3
        ON (t1.payment_id = t3.id);

The best of them is this:

 Nested Loop Left Join  (cost=1488.34..126055.47 rows=9985 width=1015)
                        (actual time=26.894..78.711 rows=10000 loops=1)
   ->  Nested Loop Left Join
       (cost=1487.91..86675.47 rows=9985 width=828)
       (actual time=26.892..72.170 rows=10000 loops=1)
         ->  Limit  (cost=1487.35..1911.50 rows=9985 width=597)
                    (actual time=26.873..33.735 rows=10000 loops=1)
               ->  Index Scan using xxx on transaction tt
                   (cost=0.57..1911.50 rows=44985 width=597)
                   (actual time=0.020..31.707 rows=45000 loops=1)
                     Index Cond: ((account_id = 1376641::bigint) AND
                                  (transaction_time >= '...') AND
                                  (transaction_time < '...'))
         ->  Index Scan using pk_fmb on fmb t2
             (cost=0.56..8.47 rows=1 width=231)
             (actual time=0.003..0.003 rows=1 loops=10000)
               Index Cond: (tt.fmb_id = id)
   ->  Index Scan using pk_payment on payment t3
       (cost=0.43..3.93 rows=1 width=187)
       (actual time=0.000..0.000 rows=0 loops=10000)
         Index Cond: (tt.payment_id = id)
 Total runtime: 79.219 ms

Another one is this:

 Hash Left Join  (cost=55139.59..140453.16 rows=9985 width=1015)
                 (actual time=715.450..762.989 rows=10000 loops=1)
   Hash Cond: (tt.payment_id = t3.id)
   ->  Nested Loop Left Join
       (cost=1487.91..86675.47 rows=9985 width=828)
       (actual time=27.472..70.723 rows=10000 loops=1)
         ->  Limit  (cost=1487.35..1911.50 rows=9985 width=597)
                    (actual time=27.453..34.066 rows=10000 loops=1)
               ->  Index Scan using xxx on transaction tt
                   (cost=0.57..1911.50 rows=44985 width=597)
                   (actual time=0.076..32.050 rows=45000 loops=1)
                     Index Cond: ((account_id = 1376641::bigint) AND
                                  (transaction_time >= '...') AND
                                  (transaction_time < '...'))
         ->  Index Scan using pk_fmb on fmb t2
             (cost=0.56..8.47 rows=1 width=231)
             (actual time=0.003..0.003 rows=1 loops=10000)
               Index Cond: (tt.fmb_id = id)
   ->  Hash  (cost=40316.30..40316.30 rows=1066830 width=187)
             (actual time=687.651..687.651 rows=1066830 loops=1)
         Buckets: 131072  Batches: 1  Memory Usage: 235206kB
         ->  Seq Scan on payment t3
             (cost=0.00..40316.30 rows=1066830 width=187)
             (actual time=0.004..147.681 rows=1066830 loops=1)
 Total runtime: 781.584 ms


You see this 2nd plan takes 10 times longer.

Now, if I

  set enable_seqscan=off;

the planner generates the 1st plan also for this parameter set and it
executes in about the same time (~80 ms).

Then I created a new tablespace with very low cost settings:

  alter tablespace trick_indexes set
    (seq_page_cost=0.0001, random_page_cost=0.0001);

and moved the pk_payment there. The tablespace is located on the same
disk. The only reason for it's existence are the differing cost parameters.

Now I could turn enable_seqscan back on and still got the better query plan.


Is there an other way to make the planner use generate the 1st plan?

Why does it generate the 2nd plan at all?

Does the planner take into account what is currently present in shared
memory? If so, it could know that the pk_payment index is probably in
RAM most of the time.


Thanks,
Torsten