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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
