Thanks Justin.
The 4ms different in the examples isn't an accurate benchmark. I'm seeing
about a ~20% difference over a larger sample size. And this is on a fork of
the production database.
Apart from the end-performance, I'm motivated to figure out why one index
results in an index scan whereas
On Tue, Feb 19, 2019 at 05:10:43PM -0700, Abi Noda wrote:
> I have a table as defined below. The table contains 1,027,616 rows, 50,349
> of which have state='open' and closed IS NULL. Since closed IS NULL for all
> rows where state='open', I want to remove the unnecessary state column.
>
> CREATE
On Tue, Feb 19, 2019, 8:00 PM Andrew Gierth > "Abi" == Abi Noda writes:
>
> Abi> However, when I index the closed column, a bitmap scan is used
> Abi> instead of an index scan, with slightly slower performance. Why
> Abi> isn't an index scan being used, given that the exact same number
>
> "Abi" == Abi Noda writes:
Abi> However, when I index the closed column, a bitmap scan is used
Abi> instead of an index scan, with slightly slower performance. Why
Abi> isn't an index scan being used, given that the exact same number
Abi> of rows are at play as in my query on the state c
Thank you for the help.
> If the "closed_index" index is large than the "state_index", then doing
an Index scan on "closed_index" is going to be costed higher.
FWIW, both indexes appear to be the same size:
select pg_size_pretty(pg_relation_size('state_index'));
1144 kB
select pg_size_pretty(pg
On Wed, 20 Feb 2019 at 13:11, Abi Noda wrote:
> However, when I index the closed column, a bitmap scan is used instead of an
> index scan, with slightly slower performance. Why isn't an index scan being
> used, given that the exact same number of rows are at play as in my query on
> the state c
*Postgres version: PostgreSQL 10.3 on x86_64-apple-darwin16.7.0*
*Operating system and version: MacOS v10.12.6*
*How you installed PostgreSQL: Homebrew*
I have a table as defined below. The table contains 1,027,616 rows, 50,349
of which have state='open' and closed IS NULL. Since closed IS NULL fo
Thanks for the feedback!
On Tue, Feb 19, 2019, 6:42 PM Michael Lewis I would expect that R2 vs R3 would be negligible but perhaps gist works
> much better and would be an improvement. When you are down to 7ms already,
> I wouldn't hope for any big change. I assume you used btree for the
> multi-c
I would expect that R2 vs R3 would be negligible but perhaps gist works
much better and would be an improvement. When you are down to 7ms already,
I wouldn't hope for any big change. I assume you used btree for the
multi-column index on R2 range_first, range_last but am not familiar with
gist on ra
I dont have any indexes on R (the table with the jsonb column). I was
asking if I can create any that can increase this query`s performance.
If I understood you correctly I have 3 options right now :
1)R, without indexes
2)R2 with an index on first and last
3)R3 that should contain a single range
Is your JSON data getting toasted? I wouldn't assume so if it is remaining
small but something to check. Regardless, if an index exists and isn't
being used, then that would be the primary concern. You didn't share what
the definition of the index on R.data is... what do you already have?
You have
Hi,
I have a table with json col : R(object int, data jsonb).
Example for content :
object | data
+---
50 | {"ranges": [[1, 1]]}
51 | {"ranges": [[5, 700],[1,5],[9,10}
52 | {"ranges":
Andres, Andrew, and Tom:
Thanks for your insightful comments! We conducted additional analysis based on
your comments and would like to share the results. We would also like to get
your feedback on a few design decisions to increase the utility of our
performance regression reports.
###
13 matches
Mail list logo
