Exists, limit and alternate plans
| От | Віталій Тимчишин |
|---|---|
| Тема | Exists, limit and alternate plans |
| Дата | |
| Msg-id | AANLkTikRAHA-kTh5q65zdSQvaiAPEewMj01yf16mNPIa@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Exists, limit and alternate plans
|
| Список | pgsql-performance |
Hello.
--
Best regards,
Vitalii Tymchyshyn
Today I've found out strange results for query below.
select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.2 on amd64-portbld-freebsd8.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit
--Original query:
explain analyze select exists(select * from investor i where i.company_id = this_.id) from COMPANY this_ order by this_.rank desc, this_.id asc limit 10;
Limit (cost=0.00..50.67 rows=10 width=16) (actual time=144.489..144.556 rows=10 loops=1)
-> Index Scan using comp_rank_id on company this_ (cost=0.00..34616009.08 rows=6831169 width=16) (actual time=144.484..144.524 rows=10 loops=1)
SubPlan 1
-> Index Scan using company_invs on investor i (cost=0.00..9.52 rows=2 width=0) (never executed)
Index Cond: ((company_id)::bigint = $0)
SubPlan 2
-> Seq Scan on investor i (cost=0.00..1836.17 rows=41717 width=8) (actual time=0.006..72.364 rows=41722 loops=1)
Total runtime: 144.975 ms
(8 rows)
--set enable_seqscan=false;
explain analyze select exists(select * from investor i where i.company_id = this_.id) from COMPANY this_ order by this_.rank desc, this_.id asc limit 10;
Limit (cost=0.00..50.67 rows=10 width=16) (actual time=0.045..0.177 rows=10 loops=1)
-> Index Scan using comp_rank_id on company this_ (cost=0.00..34616009.08 rows=6831169 width=16) (actual time=0.041..0.146 rows=10 loops=1)
SubPlan 1
-> Index Scan using company_invs on investor i (cost=0.00..9.52 rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=10)
Index Cond: ((company_id)::bigint = $0)
SubPlan 2
-> Seq Scan on investor i (cost=10000000000.00..10000001836.17 rows=41717 width=8) (never executed)
Total runtime: 0.253 ms
(8 rows)
--limit inside exists
explain analyze select exists(select * from investor i where i.company_id = this_.id limit 1) from COMPANY this_ order by this_.rank desc, this_.id asc limit 10;
Limit (cost=0.00..50.67 rows=10 width=16) (actual time=0.052..0.219 rows=10 loops=1)
-> Index Scan using comp_rank_id on company this_ (cost=0.00..34616009.08 rows=6831169 width=16) (actual time=0.049..0.189 rows=10 loops=1)
SubPlan 1
-> Limit (cost=0.00..4.76 rows=1 width=422) (actual time=0.011..0.011 rows=1 loops=10)
-> Index Scan using company_invs on investor i (cost=0.00..9.52 rows=2 width=422) (actual time=0.007..0.007 rows=1 loops=10)
Index Cond: ((company_id)::bigint = $0)
Total runtime: 0.291 ms
(7 rows)
So, my Qs:
1) Do we really have alternative plans for SubPlan that are selected at runtime? Wow.
2) Why "Seq scan" plan is selected by default? Is it because of outer limit not being applied when calculating costs for subplans at runtime?
3) Why does limit inside exists helps? Is it simply because new "alternative" logic in not applied for "complex case"?
Best regards,
Vitalii Tymchyshyn
В списке pgsql-performance по дате отправления: