Re: Accounting for toast in query planner. (gin/gist indexes).

> Jesper Krogh <jesper(at)krogh(dot)cc> writes:
>> I have currently hit a problem which I dug into finding the cause for,
>> in
>> particular, searching in GIN indices seems in some situations to
>> un-fairly favor Sequential Scans.
>
>> Googling a bit I found this page:
>> http://postgis.refractions.net/docs/ch06.html#id2635817
>> Describing the excact problem.
>
>> It seemed to be discussed back in the pre 8.1 days and wasn't
>> solved there, is there a chance someone may address it in 9.2 ?
>> http://archives.postgresql.org/pgsql-performance/2005-02/msg00041.php
>
> Don't hold your breath. There's a huge amount of work to do there,
> and nobody's even thinking about it. The problem has actually gotten
> worse since 8.1, because the index access patterns are more complex
> and the planner has less not more info about them (because we pushed
> the determination of what's "lossy" to run time). Accumulating stats
> about how toasty the heap tuples are would be only the first step
> towards producing better cost estimates here --- you'd also need some
> way of estimating how lossy an index is, for instance, so you could
> guess how many heap tuples will be visited. And on top of that,
> for a lot of these complicated operators even our estimates of the final
> number of matches are pretty crummy. I'm not sure if having an explicit
> model of what's happening inside the index would help. Maybe it would,
> since the condition actually being tested by the index is probably
> simpler than the operator proper, but right now the planner has no clue
> that they're different.

I dont understand the details about lossy-ness, but even with the changes
for getting a cost estimate for GIN indexes that went into 9.1 (which
made the situation quite a lot better) we still favor Sequential Scans
in situations where it is a really bad idea. The cost of doing filtering
on something that is "huge" and relies in toast are not accounted for.

Taking the same dataset to the "worst situation" is a query like: "this
key exists in all of the dataset", how many are there.. the alternative
query-plans are here:

2011-12-01 09:59:46.428 jk=# explain (analyze on, buffers on) select
count(id) from ftstest where fts @@ to_tsquery('english','test1');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9591.79..9591.80 rows=1 width=4) (actual
time=109.084..109.084 rows=1 loops=1)
Buffers: shared hit=1 read=4595
-> Bitmap Heap Scan on ftstest (cost=2149.59..9091.92 rows=199947
width=4) (actual time=38.613..87.784 rows=199939 loops=1)
Recheck Cond: (fts @@ '''test1'''::tsquery)
Buffers: shared hit=1 read=4595
-> Bitmap Index Scan on ftstest_fts_idx1 (cost=0.00..2099.60
rows=199947 width=0) (actual time=37.847..37.847 rows=199939
loops=1)
Index Cond: (fts @@ '''test1'''::tsquery)
Buffers: shared hit=1 read=152
Total runtime: 109.129 ms
(9 rows)

Time: 109.842 ms
2011-12-01 10:02:19.047 jk=# set enable_seqscan to on;
SET
Time: 0.122 ms
2011-12-01 10:02:23.657 jk=# explain (analyze on, buffers on) select
count(id) from ftstest where fts @@ to_tsquery('english','test1');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7442.87..7442.88 rows=1 width=4) (actual
time=25266.047..25266.048 rows=1 loops=1)
Buffers: shared hit=651448 read=561776
-> Seq Scan on ftstest (cost=0.00..6943.00 rows=199947 width=4)
(actual time=0.015..25177.959 rows=199939 loops=1)
Filter: (fts @@ '''test1'''::tsquery)
Rows Removed by Filter: 61
Buffers: shared hit=651448 read=561776
Total runtime: 25266.080 ms
(7 rows)

Time: 25266.740 ms
2011-12-01 10:02:49.936 jk=#

For FTS it hits double, since the tsvector coloum "typically" is in TOAST
(since it is large) and "rarely" is in memory (since it is rarely returned
to the user or used for anything except from building the index).

As can be seen, it hits 651448 buffers in the latter (default) query
and only 4596 in the former. Would just "naively" accounting for the
need of TOAST-access level that x10 difference out?

Secondly I could bump the default cost of ts_match_vq/ts_match_qv a
bit up, since the cost of doing that computation is probably not as
cheap as a ordinary boolean function.

I'm not trying to argue that the solution is simple, since my insights
are limited there, only that the problem "forces" me to drop in
"set enable_seqscan to off" in random places in the code. (which
i definately would prefer to go without).

Jesper

--
Jesper