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

时间： 2024-10-25 18:19:46

postgresql 优化 order by 对索引使用的影响的相关文章

mysql索引提高优化order by语句用法介绍

先我们要注意一下 1>mysql一次查询只能使用一个索引.如果要对多个字段使用索引,建立复合索引. 2>在ORDER BY操作中,MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引. 关于索引一些说法 MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度. MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作. 通过索引优化来实现MySQL的ORDER BY语句优化: 1.ORDER BY的索引

起源自天文学的PostgreSQL 优化器成本因子校对

标签 PostgreSQL , 优化器 , 成本因子 , 校对背景最近在写一个PostgreSQL数据库培训PPT, 其中有explain的讲解需求, 刚刚接触PostgreSQL的童鞋对PostgreSQL的explain输出中cost的部分可能会一头雾水, 看不懂cost的值和SQL实际运行时间有什么联系. 为了让大家能更加深刻的了解explain, 我打算对explain的cost常量做一次校准, 这个校准的灵感来自天文望远镜赤道仪的校准. PostgreSQL cost常量在校准后,

PostgreSQL 优化器逻辑推理能力源码解析

数据库优化器需要具备逻辑推理能力,而且越强越好,为什么呢?举一些例子,通过已知的一个人讲的是真话,推理另一个人讲的一定是真话或一定是假话.例子1: 假设预先提供了 a > 10 是真话可以推理出 a < 1 一定是假话例子2: 假设预先提供了 a > 10 是真话无法推理出 a < 100 一定是真话或假话例子3: 假设预先提供了 a 是空是真话可以推理出 a 不是空一定是假话例子4: 假设预先提供了 a <>100 是真话可以推理出 a =100 一定是假

[MySQL 5.6优化] --order by limit x,x 优化

简介: order by limit x ,x 在实际工作中有各种业务需求要有order by的排序,有时候处理不好则会造成系统宕机! 原理: a.通过索引来获取排序 b.通过内部算法获取排序: 案例具体SQL: SELECT c.order_price orderPrice, c.preferential_amount preferentialAmount, c.order_sumprice orderSumprice, cast(c.mode as SIGNED) rechargeType

【SQL 学习】排序问题之order by与索引排序

SQL> conn yang/yang as sysdba 已连接. SQL> create table t as select object_id id ,object_name name 2 from dba_objects ; 表已创建. SQL> set autot traceonly 一次普通的全表扫描,没有排序的! SQL> select id ,name from t; 已选择68372行. 执行计划 ------------------------------

PostgreSQL 收缩膨胀表或索引 - pg_squeeze or pg_repack

PostgreSQL 收缩膨胀表或索引 - pg_squeeze or pg_repack 作者 digoal 日期 2016-10-30 标签 PostgreSQL , pg_repack , pg_reorg , 表膨胀收缩 , 自动回收垃圾 , 自动收缩 , pg_squeeze 背景 PostgreSQL的表或索引发生膨胀后,用户可以使用vacuum full或rewrite table(如cluster)的方式重建表. 但是vacuum full或者rewrite都需要持有排它锁,会堵

MySQL教程:用索引优化ORDER BY

关于建立索引的几个准则: 1.合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度. 2.索引越多,更新数据的速度越慢. 3.尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以BTree存储索引),而不是InnoDB.但MyISAM不支持Transcation. 4.当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并不能顺利解决,那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了. 5.习惯和强迫自己用EXPLAIN来

PostgreSQL 聚集存储与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说

标签 PostgreSQL , 聚集存储 , cluster on index , brin , 轨迹数据 , 范围查询 , 线性相关性 , hbase , json , jsonb , hstore , key-value , text 背景在现实生活中,人们的各种社会活动,会产生很多的行为数据,比如购物.刷卡.打电话.开房.吃饭.玩游戏.逛网站.聊天等等. 如果可以把它当成一个虚拟现实(AR)的游戏,我们所有的行为都被记录下来了. 又比如,某些应用软件,在征得你的同意的情况下,可能会记录

postgresql 定时收集表和索引统计信息

--由于pg中表和索引的信息收集都是基于时间点的,对于以往的信息无法与现在的信息进行对比,故写下此工具进行统计信息收集 --创建数据信息的schema create schema db_stat; --创建收集信息的基础表 create table db_stat.snapshot_pg_stat_all_indexes (relid int,indexrelid int,schemaname varchar(200),relname varchar(550),indexrelname varc