On Tue, May 5, 2020 at 9:37 PM Justin Pryzby wrote:
>
> On Tue, May 05, 2020 at 08:31:29PM -0400, Arya F wrote:
> > On Mon, May 4, 2020 at 5:21 AM Justin Pryzby wrote:
> >
> > > I mentioned in February and March that you should plan to set
> > > shared_buffer
On Mon, May 4, 2020 at 5:21 AM Justin Pryzby wrote:
> I mentioned in February and March that you should plan to set shared_buffers
> to fit the indexes currently being updated.
>
The following command gives me
select pg_size_pretty (pg_indexes_size('test_table'));
pg_size_pretty
On Mon, May 4, 2020 at 12:44 AM David Rowley wrote:
> How long does it take if you first do:
>
> SET enable_nestloop TO off;
I tried this, but it takes much longer
Time: 318620.319 ms (05:18.620)
Below is the EXPLAIN ANALYZE
QUERY PLAN
On Sun, May 3, 2020 at 11:46 PM Michael Lewis wrote:
>
> What kinds of storage (ssd or old 5400 rpm)? What else is this machine
> running?
Not an SSD, but an old 1TB 7200 RPM HDD
> What configs have been customized such as work_mem or random_page_cost?
work_mem = 2403kB
random_page_cost = 1.1
I have created the following table to duplicate my performance
numbers, but I have simplified the table for this question.
I'm running PostgreSQL 12 on the following hardware.
Dual Xeon Quad-Core E5320 1.86GHz
4GB of RAM
The table structure is
id uuid
address_api_url text
check_timestamp
> * Arya F (arya6...@gmail.com) wrote:
> > I need to store about 600 million rows of property addresses across
> > multiple counties. I need to have partitioning setup on the table as
> > there will be updates and inserts performed to the table frequently
> > and I wa
I need to store about 600 million rows of property addresses across
multiple counties. I need to have partitioning setup on the table as
there will be updates and inserts performed to the table frequently
and I want the queries to have good performance.
>From what I understand hash partitioning
On Sun, Apr 5, 2020 at 2:55 PM Tom Lane wrote:
>
> Arya F writes:
> > I have a table with 120 million rows of data spread among 512
> > partitioned by hash table. The id column of the table is a uuid, which
> > is what is being used for the partition hash and it's also
I have a table with 120 million rows of data spread among 512
partitioned by hash table. The id column of the table is a uuid, which
is what is being used for the partition hash and it's also the PK for
the table.
The table has a text column, which also has a btree index on it. A
select query on
I have noticed that my write/update performance starts to dramatically
reduce after about 10 million rows on my hardware. The reason for the
slowdown is the index updates on every write/update.
The solution would be partitioning? One of my tables will have more
than 1 billion rows of data, so I
12:03:52PM -0500, Arya F wrote:
> > I'm looking to write about 1100 rows per second to tables up to 100
> million
> > rows. I'm trying to come up with a design that I can do all the writes
> to a
> > database with no indexes. When having indexes the write performance slows
I'm looking to write about 1100 rows per second to tables up to 100 million
rows. I'm trying to come up with a design that I can do all the writes to a
database with no indexes. When having indexes the write performance slows
down dramatically after the table gets bigger than 30 million rows.
I
As my table has gotten bigger, it takes longer to get a single row back
when querying a row by its btree index.
Right now the database is running on a traditional HDD. SSDs have a much
faster seek time than traditional HDDs.
Would switching to an SSD improve "Index Only Scan" time greatly? by at
13 matches
Mail list logo