--原始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