来测一下你有没有SQL优化基因!

  • 时间:
  • 浏览:25

代表了这麼 做任何优化的清况 下的查询性能。

117650248998312 群克隆链接去分享

定海神棍 群克隆链接去分享

postgres=# explain analyze select from (select a.,(select aid from b where b.aid=a.id limit 1) as aid from a ) as t where t.aid is null;

鹏ge 群克隆链接去分享

Seq Scan on a (cost=0.00..4137.84 rows=5 width=41) (actual time=18.232..18.904 rows=50 loops=1)

Filter: ((SubPlan 2) IS NULL)

Rows Removed by Filter: 901

SubPlan 1

explain (analyze,timing)with recursive temp as



(select min(aid)as aid from b where aid is not null)

union all

(select

(select min(aid)from b where b.aid > s.aid and b.aid is not null)from temp s where s.aid is not null



)

Select from a left join (select distinct aid as aid from temp) b on (a.id = b.aid) where b. is null

被人吐槽这麼 老的数据库甜得有这表现,我很吃惊

1349398120564520 群克隆链接去分享

插入测试数据

Planning time: 0.246 ms

Execution time: 17.084 ms

teewon 群克隆链接去分享

宇长龙 群克隆链接去分享

feicai 已获得优酷VIP月卡 群克隆链接去分享

打赏,打赏

117650248998312 群克隆链接去分享

下面两条SQL不是 满足查询条件的SQL,由PostgreSQL 10给出。

Seq Scan on public.a (cost=61.17..82.68 rows=50 width=37) (actual time=17.876..17.905 rows=50 loops=1)

Output: a.id, a.info

Filter: (NOT (hashed SubPlan 5))

Rows Removed by Filter: 901

Buffers: shared hit=3616

SubPlan 5

古极 群克隆链接去分享

postgres=# explain analyze select from (select a.,(select aid from b where b.aid=a.id limit 1) as aid from a ) as t where t.aid is null;

QUERY PLAN

Seq Scan on a (cost=0.00..4137.84 rows=5 width=41) (actual time=18.232..18.904 rows=50 loops=1)

Filter: ((SubPlan 2) IS NULL)

Rows Removed by Filter: 901

SubPlan 1

-> Limit (cost=0.43..4.09 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=50)

-> Index Only Scan using b_aid on b (cost=0.43..40614.63 rows=11099 width=4) (actual time=0.002..0.002 rows=0 loops=50)

Index Cond: (aid = a.id)

Heap Fetches: 0

SubPlan 2

-> Limit (cost=0.43..4.09 rows=1 width=4) (actual time=0.017..0.017 rows=1 lps=501)

-> Index Only Scan using b_aid on b b_1 (cost=0.43..40614.63 rows=11099 width=4) (actual time=0.017..0.017 rows=1 loops=501)

Index Cond: (aid = a.id)

Heap Fetches: 901

Planning time: 0.297 ms

Execution time: 18.950 ms

(15 rows)

厉害,大佬

让你想问话题为甚么收藏?

explain (analyze,timing) select id from a where not exists(select 1 from b where a.id = b.aid);

请使用 PostgreSQL 黑科技。

1357185084506931 群克隆链接去分享

117550094894991 群克隆链接去分享

虾米VIP季卡 x 1

五颜六色,看着很让你激动。啊

(比如说巡逻车辆ID,环卫车辆的ID,公交车,微公交的ID)。

上一篇sql赛,我学了mysql,还没玩溜,这又来postgre。。好,我认了,我抽空去学!(帮我问,有这麼 Oracle的优化啊)

中秋节到了 群克隆链接去分享

为哪此有强迫症的我,看着很重乱,很重想替他架构设计 一下?

Planning time: 0.297 ms

Execution time: 18.950 ms

(15 rows)

建表

(比如车辆的行车轨迹数据,每秒上报轨迹,数据量就非常庞大)。

无话可说