Skip to content

Instantly share code, notes, and snippets.

@charles-cooper

charles-cooper/filtered_query.plan

Last active July 15, 2016 18:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save charles-cooper/abccf41ce5a1175635414321622fba57 to your computer and use it in GitHub Desktop.
Save charles-cooper/abccf41ce5a1175635414321622fba57 to your computer and use it in GitHub Desktop.
Download ZIP
Row based access query plans
Raw
filtered_query.plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.29..460.50 rows=56 width=20) (actual time=0.302..20.151 rows=14556 loops=1)
Buffers: shared hit=1034
-> Seq Scan on sm2item i (cost=0.00..4.28 rows=1 width=2) (actual time=0.049..0.055 rows=1 loops=1)
Filter: (item = 161)
Rows Removed by Filter: 181
Buffers: shared hit=2
-> Nested Loop (cost=1.29..455.39 rows=56 width=22) (actual time=0.234..16.123 rows=14556 loops=1)
Buffers: shared hit=1032
-> Nested Loop (cost=0.72..268.41 rows=1 width=16) (actual time=0.122..1.246 rows=53 loops=1)
Buffers: shared hit=289
-> Index Scan using vw_secmap_ventype_eid_idx on vw_secmap s (cost=0.43..251.13 rows=4 width=12) (actual time=0.082..0.710 rows=53 loops=1)
Index Cond: ((ventype = 23) AND (eid = ANY ('{1011103444,1011104467,1011107316,1011107487,1011108370,1011110098,1011113327,1011118705,1011123638,1011129247,1011129271,1011133380,1011133542,1011133548,1011133769,1011134126,1011135165,1011135316,1011135554,1011136443,1011136514,1011136531,1011136747,1011137714,1011137923,1011140099,1011141027,1011141091,1011141262,1011141279,1011141583,1011142199,1011142848,1011143861,1011143894,1011144465,1011147311,1011148404,1011149266,1011149294,1011149443,1011149457,1011149576,1011150581,1011150590,1011151010,1011152042,1011153318,1011153534,1011153782,1011157053,1011158430,1011158763}'::bigint[])))
Buffers: shared hit=182
-> Index Only Scan using sm2dinfo_pkey on sm2dinfo si (cost=0.29..4.31 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=53)
Index Cond: (secid = s.vencode)
Heap Fetches: 0
Buffers: shared hit=107
-> Index Scan using sm2dvmoam_pkey on sm2dvmoam tbl (cost=0.57..168.56 rows=1842 width=18) (actual time=0.032..0.233 rows=275 loops=53)
Index Cond: ((item = 161) AND (secid = si.secid) AND (startdate >= '2000-12-31 00:00:00'::timestamp without time zone) AND (startdate <= '2100-01-01 00:00:00'::timestamp without time zone))
Filter: ((licflag = 1) OR (licflag = 3) OR (licflag = 2) OR (licflag = 3))
Buffers: shared hit=743
Planning time: 3.163 ms
Execution time: 21.171 ms
(23 rows)
Raw
filtered_query.sql
EXPLAIN (ANALYZE, BUFFERS)
select
s.eid
, cast(foo.startdate as date)
, foo.value_::float8
from vw_secmap s
join lateral (
select tbl.startdate, tbl.value_
from sm2DVMOam tbl
join sm2item i
on i.item = tbl.item
join sm2dinfo si ON
si.secid = tbl.secid
where si.secid = s.vencode
and tbl.item = 161 --starmine code for table
and tbl.startdate between '2000-12-31' and '2100-01-01'
and (licflag = 1 OR licflag = 3 OR licflag = 2 OR licflag = 3)
) foo on
ventype = 23
--and s.rank=1
where s.eid in (1011103444,1011104467,1011107316,1011107487,1011108370,1011110098,1011113327,1011118705,1011123638,1011129247,1011129271,1011133380,1011133542,1011133548,1011133769,1011134126,1011135165,1011135316,1011135554,1011136443,1011136514,1011136531,1011136747,1011137714,1011137923,1011140099,1011141027,1011141091,1011141262,1011141279,1011141583,1011142199,1011142848,1011143861,1011143894,1011144465,1011147311,1011148404,1011149266,1011149294,1011149443,1011149457,1011149576,1011150581,1011150590,1011151010,1011152042,1011153318,1011153534,1011153782,1011157053,1011158430,1011158763) --seccode
Raw
original_query.plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.29..442.22 rows=65 width=20) (actual time=0.293..17.936 rows=14556 loops=1)
Buffers: shared hit=1034
-> Seq Scan on sm2item i (cost=0.00..4.28 rows=1 width=2) (actual time=0.049..0.055 rows=1 loops=1)
Filter: (item = 161)
Rows Removed by Filter: 181
Buffers: shared hit=2
-> Nested Loop (cost=1.29..436.97 rows=65 width=22) (actual time=0.224..13.539 rows=14556 loops=1)
Buffers: shared hit=1032
-> Nested Loop (cost=0.72..268.41 rows=1 width=16) (actual time=0.126..1.242 rows=53 loops=1)
Buffers: shared hit=289
-> Index Scan using vw_secmap_ventype_eid_idx on vw_secmap s (cost=0.43..251.13 rows=4 width=12) (actual time=0.077..0.710 rows=53 loops=1)
Index Cond: ((ventype = 23) AND (eid = ANY ('{1011103444,1011104467,1011107316,1011107487,1011108370,1011110098,1011113327,1011118705,1011123638,1011129247,1011129271,1011133380,1011133542,1011133548,1011133769,1011134126,1011135165,1011135316,1011135554,1011136443,1011136514,1011136531,1011136747,1011137714,1011137923,1011140099,1011141027,1011141091,1011141262,1011141279,1011141583,1011142199,1011142848,1011143861,1011143894,1011144465,1011147311,1011148404,1011149266,1011149294,1011149443,1011149457,1011149576,1011150581,1011150590,1011151010,1011152042,1011153318,1011153534,1011153782,1011157053,1011158430,1011158763}'::bigint[])))
Buffers: shared hit=182
-> Index Only Scan using sm2dinfo_pkey on sm2dinfo si (cost=0.29..4.31 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=53)
Index Cond: (secid = s.vencode)
Heap Fetches: 0
Buffers: shared hit=107
-> Index Scan using sm2dvmoam_pkey on sm2dvmoam tbl (cost=0.57..147.05 rows=2151 width=18) (actual time=0.032..0.173 rows=275 loops=53)
Index Cond: ((item = 161) AND (secid = si.secid) AND (startdate >= '2000-12-31 00:00:00'::timestamp without time zone) AND (startdate <= '2100-01-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=743
Planning time: 3.150 ms
Execution time: 18.999 ms
(22 rows)
Raw
original_query.sql
EXPLAIN (ANALYZE, BUFFERS)
select
s.eid
, cast(foo.startdate as date)
, foo.value_::float8
from vw_secmap s
join lateral (
select tbl.startdate, tbl.value_
from sm2DVMOam tbl
join sm2item i
on i.item = tbl.item
join sm2dinfo si ON
si.secid = tbl.secid
where si.secid = s.vencode
and tbl.item = 161 --starmine code for table
and tbl.startdate between '2000-12-31' and '2100-01-01'
) foo on
ventype = 23
--and s.rank=1
where s.eid in (1011103444,1011104467,1011107316,1011107487,1011108370,1011110098,1011113327,1011118705,1011123638,1011129247,1011129271,1011133380,1011133542,1011133548,1011133769,1011134126,1011135165,1011135316,1011135554,1011136443,1011136514,1011136531,1011136747,1011137714,1011137923,1011140099,1011141027,1011141091,1011141262,1011141279,1011141583,1011142199,1011142848,1011143861,1011143894,1011144465,1011147311,1011148404,1011149266,1011149294,1011149443,1011149457,1011149576,1011150581,1011150590,1011151010,1011152042,1011153318,1011153534,1011153782,1011157053,1011158430,1011158763) --seccode
Raw
row-based_query.plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=809369.94..3453685.86 rows=56 width=20) (actual time=3914.370..11950.555 rows=14556 loops=1)
Buffers: shared hit=301690
-> Seq Scan on sm2item i (cost=0.00..4.28 rows=1 width=2) (actual time=0.064..0.069 rows=1 loops=1)
Filter: (item = 161)
Rows Removed by Filter: 181
Buffers: shared hit=2
-> Hash Join (cost=809369.94..3453680.75 rows=56 width=22) (actual time=3914.286..11947.449 rows=14556 loops=1)
Hash Cond: (tbl.secid = si.secid)
Buffers: shared hit=301688
-> Bitmap Heap Scan on sm2dvmoam tbl (cost=809101.52..3151195.86 rows=21979157 width=18) (actual time=3128.354..9915.334 rows=24819619 loops=1)
Recheck Cond: ((item = 161) AND (startdate >= '2000-12-31 00:00:00'::timestamp without time zone) AND (startdate <= '2100-01-01 00:00:00'::timestamp without time zone))
Filter: ((licflag = 3) OR (licflag = 2) OR (licflag = 3) OR (licflag = 1))
Heap Blocks: exact=192031
Buffers: shared hit=301396
-> Bitmap Index Scan on sm2dvmoam_pkey (cost=0.00..803606.73 rows=25654594 width=0) (actual time=3037.394..3037.394 rows=24827083 loops=1)
Index Cond: ((item = 161) AND (startdate >= '2000-12-31 00:00:00'::timestamp without time zone) AND (startdate <= '2100-01-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=109365
-> Hash (cost=268.41..268.41 rows=1 width=16) (actual time=1.539..1.539 rows=53 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Buffers: shared hit=289
-> Nested Loop (cost=0.72..268.41 rows=1 width=16) (actual time=0.151..1.508 rows=53 loops=1)
Buffers: shared hit=289
-> Index Scan using vw_secmap_ventype_eid_idx on vw_secmap s (cost=0.43..251.13 rows=4 width=12) (actual time=0.100..0.877 rows=53 loops=1)
Index Cond: ((ventype = 23) AND (eid = ANY ('{1011103444,1011104467,1011107316,1011107487,1011108370,1011110098,1011113327,1011118705,1011123638,1011129247,1011129271,1011133380,1011133542,1011133548,1011133769,1011134126,1011135165,1011135316,1011135554,1011136443,1011136514,1011136531,1011136747,1011137714,1011137923,1011140099,1011141027,1011141091,1011141262,1011141279,1011141583,1011142199,1011142848,1011143861,1011143894,1011144465,1011147311,1011148404,1011149266,1011149294,1011149443,1011149457,1011149576,1011150581,1011150590,1011151010,1011152042,1011153318,1011153534,1011153782,1011157053,1011158430,1011158763}'::bigint[])))
Buffers: shared hit=182
-> Index Only Scan using sm2dinfo_pkey on sm2dinfo si (cost=0.29..4.31 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=53)
Index Cond: (secid = s.vencode)
Heap Fetches: 0
Buffers: shared hit=107
Planning time: 3.417 ms
Execution time: 11953.125 ms
(31 rows)
Raw
row-based_query.sql
SET ROLE "qai_U0002";
EXPLAIN (ANALYZE, BUFFERS)
select
s.eid
, cast(foo.startdate as date)
, foo.value_::float8
from vw_secmap s
join lateral (
select tbl.startdate, tbl.value_
from sm2DVMOam tbl
join sm2item i
on i.item = tbl.item
join sm2dinfo si ON
si.secid = tbl.secid
where si.secid = s.vencode
and tbl.item = 161 --starmine code for table
and tbl.startdate between '2000-12-31' and '2100-01-01'
) foo on
ventype = 23
--and s.rank=1
where s.eid in (1011103444,1011104467,1011107316,1011107487,1011108370,1011110098,1011113327,1011118705,1011123638,1011129247,1011129271,1011133380,1011133542,1011133548,1011133769,1011134126,1011135165,1011135316,1011135554,1011136443,1011136514,1011136531,1011136747,1011137714,1011137923,1011140099,1011141027,1011141091,1011141262,1011141279,1011141583,1011142199,1011142848,1011143861,1011143894,1011144465,1011147311,1011148404,1011149266,1011149294,1011149443,1011149457,1011149576,1011150581,1011150590,1011151010,1011152042,1011153318,1011153534,1011153782,1011157053,1011158430,1011158763) --seccode
Raw
table_description
Table "data.sm2dvmoam"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+-----------------------------+-----------+---------+--------------+-------------
item | smallint | not null | plain | |
secid | integer | not null | plain | |
startdate | timestamp without time zone | not null | plain | |
enddate | timestamp without time zone | | plain | |
value_ | real | | plain | |
licflag | smallint | not null | plain | |
Indexes:
"sm2dvmoam_pkey" PRIMARY KEY, btree (item, secid, startdate)
"sm2dvmoam_secid_item_idx" btree (secid, item)
Policies:
POLICY "policy_qai_U0002_56000_1" FOR SELECT
TO qai_U0002
USING (((licflag = 1) AND true))
POLICY "policy_qai_U0002_56000_3" FOR SELECT
TO qai_U0002
USING (((licflag = 3) AND true))
POLICY "policy_qai_U0002_56300_2" FOR SELECT
TO qai_U0002
USING (((licflag = 2) AND true))
POLICY "policy_qai_U0002_56300_3" FOR SELECT
TO qai_U0002
USING (((licflag = 3) AND true))
elsendb=> \dt+ sm2dvmoam
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+-------+-------+-------------
data | sm2dvmoam | table | app | 12 GB |
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment