From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Bitmap scan is undercosted? - overestimated correlation and cost_index |
Date: | 2017-12-16 19:18:38 |
Message-ID: | 20171216191838.GA16769@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2017-12-16 20:30:21 | Re: [sqlsmith] Parallel worker executor crash on master |
Previous Message | Tom Lane | 2017-12-16 15:27:11 | Re: Reproducible builds: genbki.pl vs schemapg.h |
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2017-12-17 02:37:01 | Re: Bitmap scan is undercosted? - overestimated correlation and cost_index |
Previous Message | Justin Pryzby | 2017-12-15 20:54:06 | Re: Bitmap scan is undercosted? - overestimated correlation and cost_index |