DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=kYWvAeZ24uOVt9GkdeO28sF24G8GR7DOIIP97GaL+F3h4hcCsEXTO8HwQFi0CHoJZ8
	Us9gJOTGtzpebanBM5V1lnBVp+kvq/8liY5RNLEd+YaFWCucFZ5K+wPuMuOlabIJk52u
	Ni0KnwKCkEhqf76vvRSVeI5IAMGuTsbZV9hYQ=
Message-ID: <49185EF9.6070009@gmail.com>
Date: Mon, 10 Nov 2008 17:19:05 +0100
From: =?ISO-8859-1?Q?Lutisch=E1n_Ferenc?= <lutischanf@gmail.com>
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
CC: Craig Ringer <craig@postnewspapers.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: Improve Seq scan performance
References: <4917D9C3.9060001@gmail.com>	
	<4917DAFB.8000003@postnewspapers.com.au>
	<1d709ecc0811092337q74fee9e7vc418eba9c798f3a4@mail.gmail.com>
In-Reply-To: <1d709ecc0811092337q74fee9e7vc418eba9c798f3a4@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.