postgresql 优化 order by 对索引使用的影响

 
--原始sql
SELECT
    *
FROM
    tops_order.eticket
WHERE
    (
        issue_complete_date >= '2015-10-01 00:00:00+08'
        AND issue_complete_date < '2016-03-28 00:00:00+08'
        AND is_domestic = 't'
        AND customer_id IN ('53a3bfa545cebf2700d727e3')
    )
ORDER BY create_date DESC 
OFFSET 0
LIMIT 10;
--执行计划，使用的是idx_create_date索引
Limit  (cost=0.56..13959.07 rows=10 width=2633)
  ->  Index Scan Backward using idx_create_date on eticket  (cost=0.56..9207036.72 rows=6596 width=2633)
        Filter: (is_domestic AND (issue_complete_date >= '2015-10-01 00:00:00+08'::timestamp with time zone) AND (issue_complete_date < '2016-03-28 00:00:00+08'::timestamp with time zone) AND ((customer_id)::text = '53a3bfa545cebf2700d727e3'::text))
 
--表索引信息
Indexes:
    "pk_eticket" UNIQUE CONSTRAINT, btree (id)
    "idx_create_date" btree (create_date)
    "idx_customer_id" btree (customer_id)
 
 
                    
--修改其排序方式，使用idx_customer_id索引
SELECT
    *
FROM
    tops_order.eticket
WHERE
    (
        issue_complete_date >= '2015-10-01 00:00:00+08'
        AND issue_complete_date < '2016-03-28 00:00:00+08'
        AND is_domestic = 't'
        AND customer_id IN ('53a3bfa545cebf2700d727e3')
    )
ORDER BY to_char(create_date,'yyyy-mm-dd hh24:mi:ss:ms') DESC 
OFFSET 0
LIMIT 10;
 
Limit  (cost=96657.89..96657.92 rows=10 width=2633)
  ->  Sort  (cost=96657.89..96674.38 rows=6596 width=2633)
        Sort Key: (to_char(create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text))
        ->  Bitmap Heap Scan on eticket  (cost=1819.88..96515.36 rows=6596 width=2633)
              Recheck Cond: ((customer_id)::text = '53a3bfa545cebf2700d727e3'::text)
              Filter: (is_domestic AND (issue_complete_date >= '2015-10-01 00:00:00+08'::timestamp with time zone) AND (issue_complete_date < '2016-03-28 00:00:00+08'::timestamp with time zone))
              ->  Bitmap Index Scan on idx_customer_id  (cost=0.00..1818.23 rows=25823 width=0)
                    Index Cond: ((customer_id)::text = '53a3bfa545cebf2700d727e3'::text)
 
在上面的sql中由于ORDER BY create_date DESC的原因，pg使用了idx_create_date索引，但其并不是最有效的，请问pg为什么会使用那个索引，而不使用 idx_customer_id这个索引（其更高效啊），除了我上面的方法，还有什么办法可以避免吗？

 
devflight=# set work_mem='1GB';
SET
devflight=# show work_mem;
 work_mem 
----------
 1GB
explain (analyze,verbose,costs,timing,buffers)
SELECT
    *
FROM
    tops_order.eticket
WHERE
    (
        issue_complete_date >= '2015-10-01 00:00:00+08'
        AND issue_complete_date < '2016-03-28 00:00:00+08'
        AND is_domestic = 't'
        AND customer_id IN ('53a3bfa545cebf2700d727e3')
    )
ORDER BY create_date DESC 
OFFSET 0
LIMIT 10;
--第一次执行，不考虑缓存
Limit  (cost=0.56..14494.42 rows=10 width=2628) (actual time=77326.839..423643.180 rows=10 loops=1)
   Output: id, order_type...其它字段省略
   Buffers: shared hit=778155 read=410205
   ->  Index Scan Backward using idx_create_date on tops_order.eticket  (cost=0.56..9209400.56 rows=6354 width=2628) (actual time=77326.834..423643.149 rows=10 loops=1)
         Output: id, order_type...其它字段省略
         Filter: (eticket.is_domestic AND (eticket.issue_complete_date >= '2015-10-01 00:00:00+08'::timestamp with time zone) AND (eticket.issue_complete_date < '2016-03-28 00:00:00+08'::timestamp with time zone) AND ((eticket.customer_id)::text = '53a3bfa545cebf2700d72
7e3'::text))
         Rows Removed by Filter: 1279957
         Buffers: shared hit=778155 read=410205
 Total runtime: 423643.357 ms
 --第二次执行，考虑缓存
 Limit  (cost=0.56..14494.44 rows=10 width=2628) (actual time=767.403..4279.628 rows=10 loops=1)
   Output: id, order_type...其它字段省略
   Buffers: shared hit=1189156
   ->  Index Scan Backward using idx_create_date on tops_order.eticket  (cost=0.56..9209412.56 rows=6354 width=2628) (actual time=767.400..4279.606 rows=10 loops=1)
         Output: id, order_type...其它字段省略
         Filter: (eticket.is_domestic AND (eticket.issue_complete_date >= '2015-10-01 00:00:00+08'::timestamp with time zone) AND (eticket.issue_complete_date < '2016-03-28 00:00:00+08'::timestamp with time zone) AND ((eticket.customer_id)::text = '53a3bfa545cebf2700d72
7e3'::text))
         Rows Removed by Filter: 1280127
         Buffers: shared hit=1189156
 Total runtime: 4279.777 ms
 
 
 
 
explain (analyze,verbose,costs,timing,buffers)
SELECT
    *
FROM
    tops_order.eticket
WHERE
    (
        issue_complete_date >= '2015-10-01 00:00:00+08'
        AND issue_complete_date < '2016-03-28 00:00:00+08'
        AND is_domestic = 't'
        AND customer_id IN ('53a3bfa545cebf2700d727e3')
    )
ORDER BY to_char(create_date,'yyyy-mm-dd hh24:mi:ss:ms') DESC 
OFFSET 0
LIMIT 10;
--第一次执行，不考虑缓存
 Limit  (cost=93008.42..93008.44 rows=10 width=2628) (actual time=24797.439..24797.444 rows=10 loops=1)
   Output: id, order_type, (to_char(create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text))...其它字段省略
   Buffers: shared hit=2419 read=16685
   ->  Sort  (cost=93008.42..93024.30 rows=6354 width=2628) (actual time=24797.436..24797.439 rows=10 loops=1)
         Output: id, order_type, (to_char(create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text)) ...其它字段省略
         Sort Key: (to_char(eticket.create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text))
         Sort Method: top-N heapsort  Memory: 45kB
         Buffers: shared hit=2419 read=16685
         ->  Bitmap Heap Scan on tops_order.eticket  (cost=1744.15..92871.11 rows=6354 width=2628) (actual time=1473.866..24796.587 rows=32 loops=1)
               Output: id, order_type, to_char(create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text)...其它字段省略
               Recheck Cond: ((eticket.customer_id)::text = '53a3bfa545cebf2700d727e3'::text)
               Filter: (eticket.is_domestic AND (eticket.issue_complete_date >= '2015-10-01 00:00:00+08'::timestamp with time zone) AND (eticket.issue_complete_date < '2016-03-28 00:00:00+08'::timestamp with time zone))
               Rows Removed by Filter: 20282
               Buffers: shared hit=2414 read=16685
               ->  Bitmap Index Scan on idx_customer_id  (cost=0.00..1742.56 rows=24800 width=0) (actual time=1467.166..1467.166 rows=20314 loops=1)
                     Index Cond: ((eticket.customer_id)::text = '53a3bfa545cebf2700d727e3'::text)
                     Buffers: shared hit=1 read=387
 Total runtime: 24798.126 ms
 --第二次执行，考虑缓存
  Limit  (cost=93008.42..93008.44 rows=10 width=2628) (actual time=90.570..90.576 rows=10 loops=1)
   Output: id, order_type, (to_char(create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text)) ...其它字段省略
   Buffers: shared hit=19099
   ->  Sort  (cost=93008.42..93024.30 rows=6354 width=2628) (actual time=90.567..90.569 rows=10 loops=1)
         Output: id, order_type, (to_char(create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text)) ...其它字段省略
         Sort Key: (to_char(eticket.create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text))
         Sort Method: top-N heapsort  Memory: 45kB
         Buffers: shared hit=19099
         ->  Bitmap Heap Scan on tops_order.eticket  (cost=1744.15..92871.11 rows=6354 width=2628) (actual time=13.735..90.135 rows=32 loops=1)
               Output: id, order_type, to_char(create_date, 'yyyy-mm-dd hh24:mi:ss:ms'::text) ...其它字段省略
               Recheck Cond: ((eticket.customer_id)::text = '53a3bfa545cebf2700d727e3'::text)
               Filter: (eticket.is_domestic AND (eticket.issue_complete_date >= '2015-10-01 00:00:00+08'::timestamp with time zone) AND (eticket.issue_complete_date < '2016-03-28 00:00:00+08'::timestamp with time zone))
               Rows Removed by Filter: 20282
               Buffers: shared hit=19099
               ->  Bitmap Index Scan on idx_customer_id  (cost=0.00..1742.56 rows=24800 width=0) (actual time=8.259..8.259 rows=20314 loops=1)
                     Index Cond: ((eticket.customer_id)::text = '53a3bfa545cebf2700d727e3'::text)
                     Buffers: shared hit=388
 Total runtime: 90.875 ms