From: Lutischán Ferenc <lutischanf@gmail.com>
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improve Seq scan performance
Date: Mon, 10 Nov 2008 17:19:05 +0100
Message-ID: <49185EF9.6070009@gmail.com> (raw)
In-Reply-To: <1d709ecc0811092337q74fee9e7vc418eba9c798f3a4@mail.gmail.com>
References: <4917D9C3.9060001@gmail.com>
<4917DAFB.8000003@postnewspapers.com.au>
<1d709ecc0811092337q74fee9e7vc418eba9c798f3a4@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lutischanf@gmail.com, sitnikov.vladimir@gmail.com, craig@postnewspapers.com.au
Subject: Re: Improve Seq scan performance
In-Reply-To: <49185EF9.6070009@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox