Dear Vladimir,
Thanks for clear description of the problem. :-)
Please report it to the bug list.
I hope it will be accepted as a "performance bug" and will be solved.
Best Regards,
Ferenc
Vladimir Sitnikov wrotte:
>
> As far as I understand, it is discouraged to implement/suggest patches
> during Commitfest, however, I would love to treat the following case
> as a "performance bug" and add it to the "TODO" list:
>
>
> create table seq_test
> as select cast(i as text) i, repeat('*', 500) padding from
> generate_series(1,10000) as s(i);
>
> create index i_ix on seq_test(i);
>
> vacuum analyze verbose seq_test;
> -- index "i_ix" now contains 10000 row versions in *30 *pages
> -- "seq_test": found 0 removable, 10000 nonremovable row versions in
> *667 *pages
>
> explain analyze select * from seq_test where i like '%123%';
> -- Seq Scan reads 667 pages (as expected)
> Seq Scan on seq_test (cost=0.00..792.00 rows=356 width=508) (actual
> time=0.129..9.071 rows=20 loops=1 read_shared=*667*(667)
> read_local=0(0) flush=0 local_flush=0 file_read=0 file_write=0)
> Filter: (i ~~ '%123%'::text)
> Total runtime: 9.188 ms
>
> set enable_seqscan=off
> -- Index Scan reads 2529 pages for some reason. I would expect *30
> *(index size) + *20 *(number of matching entries) = 50 pages maximum,
> that is 10 times better than with seq scan.
> Index Scan using i_ix on seq_test (cost=0.00..1643.74 rows=356
> width=508) (actual time=0.334..16.746 rows=*20 *loops=1
> read_shared=2529(2529) read_local=0(0) flush=0 local_flush=0
> file_read=0 file_write=0)
> Filter: (i ~~ '%123%'::text)
> Total runtime: 16.863 ms
>
> Hopefully, there will be a clear distinction between filtering via
> index and filtering via table access.
