From: Justin Pryzby <pryzby@telsasoft.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Vitaliy Garnashevich <vgarnashevich@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bitmap scan is undercosted? - overestimated correlation and cost_index
Date: Sat, 16 Dec 2017 13:18:38 -0600
Message-ID: <20171216191838.GA16769@telsasoft.com> (raw)
In-Reply-To: <20171215205406.GN18184@telsasoft.com>
References: <18188.1512258290@sss.pgh.pa.us>
<CAMkU=1yKK0M4W+bNc31PZhH15dzPakSmpr6TH9exT8OUZvmg_w@mail.gmail.com>
<20171203040430.GC14008@telsasoft.com>
<CAMkU=1xQs6=fB+W+=3suB0ZeGsNi8ekBqEqXQ8FriTPw5kcuVQ@mail.gmail.com>
<12766.1512343900@sss.pgh.pa.us>
<CAMkU=1yCX_WK0KYUOhRSKG2a771kiK+QWU5YXxt3EPhEXCLDQQ@mail.gmail.com>
<14438.1512499811@sss.pgh.pa.us>
<20171206214652.GA13889@telsasoft.com>
<CAMkU=1x+c9+ffEXCtU7s+P15EvvHaO4h=bR2REBg5N56ubG9HQ@mail.gmail.com>
<20171215205406.GN18184@telsasoft.com>
On Fri, Dec 15, 2017 at 02:54:06PM -0600, Justin Pryzby wrote:
> SSD: good question.
>
> Here's an rackspace VM with PG9.6.6, 2GB shared_buffers, 8GB RAM (~4GB of which
> is being used as OS page cache), and 32GB SSD (with random_page_cost=1). The
> server is in use by our application.
>
> I believe you could scale up the size of the table to see this behavior with
> any cache size. 0.0001 controls the "jitter", with smaller values being more
> jittery..
>
> postgres=# CREATE TABLE t(i int,j int) TABLESPACE tmp; CREATE INDEX ON t(i); INSERT INTO t SELECT (0.0001*a+9*(random()-0.5))::int FROM generate_series(1,99999999) a; VACUUM ANALYZE t;
> public | t | table | pryzbyj | 3458 MB |
> relpages | 442478
I realized I've made a mistake here; the table is on SSD but not its index...
So all this cost is apparently coming from the index and not the heap.
-> Bitmap Heap Scan on t (cost=855041.91..1901994.06 rows=40298277 width=4) (actual time=14202.624..27754.982 rows=40009853 loops=1)
-> Bitmap Index Scan on t_i_idx1 (cost=0.00..844967.34 rows=40298277 width=0) (actual time=14145.877..14145.877 rows=40009853 loops=1)
Let me get back to you about that.
Justin
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: pryzby@telsasoft.com, jeff.janes@gmail.com, tgl@sss.pgh.pa.us, vgarnashevich@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Bitmap scan is undercosted? - overestimated correlation and cost_index
In-Reply-To: <20171216191838.GA16769@telsasoft.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