On 28 August 2017 at 21:32, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
> On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys <haramrae@xxxxxxxxx> wrote:
>>
>> Hi all,
>>
>> It's been a while since I actually got to use PG for anything serious,
>> but we're finally doing some experimentation @work now to see if it is
>> suitable for our datawarehouse. So far it's been doing well, but there
>> is a particular type of query I run into that I expect we will
>> frequently use and that's choosing a sequential scan - and I can't
>> fathom why.
>>
>> This is on:
>>
>>
>> The query in question is:
>> select "VBAK_MANDT", max("VBAK_VBELN")
>> from staging.etl00001_vbak
>> group by "VBAK_MANDT";
>>
>> This is the header-table for another detail table, and in this case
>> we're already seeing a seqscan. The thing is, there are 15M rows in
>> the table (disk usage is 15GB), while the PK is on ("VBAK_MANDT",
>> "VBAK_VBELN") with very few distinct values for "VBAK_MANDT" (in fact,
>> we only have 1 at the moment!).
>
>
> You need an "index skip-scan" or "loose index scan". PostgreSQL doesn't
> currently detect and implement them automatically, but you can use a
> recursive CTE to get it to work. There are some examples at
> https://wiki.postgresql.org/wiki/Loose_indexscan
Thanks Jeff, that's an interesting approach. It looks very similar to
correlated subqueries.
Unfortunately, it doesn't seem to help with my issue. The CTE is
indeed fast, but when querying the results from the 2nd level ov the
PK with the CTE results, I'm back at a seqscan on pdw00002_vbak again.
Just the CTE plan is in skipScan-top.sql.
The complete plan is in skipScan-full.sql
Note: I cloned the original etl00001_vbak table into a new
pdw00002_vbak table that has varchars instead of chars, which reduced
the table size to just over half the original's size. Hence the
different table names, but the behaviour for this particular issue is
the same between them.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
warehouse=# explain (analyze, buffers)
with recursive t as (
select min("VBAK_MANDT") as "VBAK_MANDT" from staging.pdw00002_vbak
union all
select (select min("VBAK_MANDT") as "VBAK_MANDT" from staging.pdw00002_vbak where "VBAK_MANDT" > t."VBAK_MANDT") from t where t."VBAK_MANDT" is not null
) select "VBAK_MANDT" from t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on t (cost=98.31..100.33 rows=101 width=32) (actual time=0.031..0.054 rows=2 loops=1)
Buffers: shared hit=9
CTE t
-> Recursive Union (cost=0.73..98.31 rows=101 width=32) (actual time=0.029..0.052 rows=2 loops=1)
Buffers: shared hit=9
-> Result (cost=0.73..0.74 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=1)
Buffers: shared hit=5
InitPlan 3 (returns $1)
-> Limit (cost=0.56..0.73 rows=1 width=32) (actual time=0.026..0.027 rows=1 loops=1)
Buffers: shared hit=5
-> Index Only Scan using pdw00002_vbak_pkey on pdw00002_vbak pdw00002_vbak_1 (cost=0.56..2375293.75 rows=14214332 width=32) (actual time=0.024..0.024 rows=1 loops=1)
Index Cond: ("VBAK_MANDT" IS NOT NULL)
Heap Fetches: 1
Buffers: shared hit=5
-> WorkTable Scan on t t_1 (cost=0.00..9.56 rows=10 width=32) (actual time=0.009..0.010 rows=0 loops=2)
Filter: ("VBAK_MANDT" IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared hit=4
SubPlan 2
-> Result (cost=0.93..0.94 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1)
Buffers: shared hit=4
InitPlan 1 (returns $3)
-> Limit (cost=0.56..0.93 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1)
Buffers: shared hit=4
-> Index Only Scan using pdw00002_vbak_pkey on pdw00002_vbak (cost=0.56..1732075.91 rows=4738111 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (("VBAK_MANDT" IS NOT NULL) AND ("VBAK_MANDT" > t_1."VBAK_MANDT"))
Heap Fetches: 0
Buffers: shared hit=4
Planning time: 0.346 ms
Execution time: 0.100 ms
(30 rows)
warehouse=# explain (analyze, buffers)
with recursive t as (
select min("VBAK_MANDT") as "VBAK_MANDT" from staging.pdw00002_vbak
union all
select (select min("VBAK_MANDT") as "VBAK_MANDT" from staging.pdw00002_vbak where "VBAK_MANDT" > t."VBAK_MANDT") from t where t."VBAK_MANDT" is not null
) select max("VBAK_VBELN") from staging.pdw00002_vbak where "VBAK_MANDT" in (select "VBAK_MANDT" from t);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1169050.81..1169050.82 rows=1 width=32) (actual time=17195.201..17195.202 rows=1 loops=1)
Buffers: shared hit=170 read=795662
CTE t
-> Recursive Union (cost=0.73..98.31 rows=101 width=32) (actual time=0.031..0.053 rows=2 loops=1)
Buffers: shared hit=9
-> Result (cost=0.73..0.74 rows=1 width=32) (actual time=0.031..0.031 rows=1 loops=1)
Buffers: shared hit=5
InitPlan 3 (returns $1)
-> Limit (cost=0.56..0.73 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=1)
Buffers: shared hit=5
-> Index Only Scan using pdw00002_vbak_pkey on pdw00002_vbak pdw00002_vbak_2 (cost=0.56..2375293.75 rows=14214332 width=32) (actual time=0.026..0.026 rows=1 loops=1)
Index Cond: ("VBAK_MANDT" IS NOT NULL)
Heap Fetches: 1
Buffers: shared hit=5
-> WorkTable Scan on t t_1 (cost=0.00..9.56 rows=10 width=32) (actual time=0.009..0.010 rows=0 loops=2)
Filter: ("VBAK_MANDT" IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared hit=4
SubPlan 2
-> Result (cost=0.93..0.94 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)
Buffers: shared hit=4
InitPlan 1 (returns $3)
-> Limit (cost=0.56..0.93 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)
Buffers: shared hit=4
-> Index Only Scan using pdw00002_vbak_pkey on pdw00002_vbak pdw00002_vbak_1 (cost=0.56..1732075.91 rows=4738111 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (("VBAK_MANDT" IS NOT NULL) AND ("VBAK_MANDT" > t_1."VBAK_MANDT"))
Heap Fetches: 0
Buffers: shared hit=4
-> Hash Semi Join (cost=3.28..1133416.67 rows=14214332 width=11) (actual time=0.088..10323.225 rows=14214672 loops=1)
Hash Cond: ((pdw00002_vbak."VBAK_MANDT")::text = t."VBAK_MANDT")
Buffers: shared hit=170 read=795662
-> Seq Scan on pdw00002_vbak (cost=0.00..937966.32 rows=14214332 width=15) (actual time=0.017..5016.514 rows=14214672 loops=1)
Buffers: shared hit=161 read=795662
-> Hash (cost=2.02..2.02 rows=101 width=32) (actual time=0.060..0.060 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=9
-> CTE Scan on t (cost=0.00..2.02 rows=101 width=32) (actual time=0.034..0.057 rows=2 loops=1)
Buffers: shared hit=9
Planning time: 0.567 ms
Execution time: 17195.336 ms
(40 rows)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
