2 million queries against a table

From: Adam Sanchez <a(dot)sanchez75(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: 2 million queries against a table
Date: 2020-07-15 15:10:30
Message-ID: CAEP=qeOSNniLWhCKkyU774xFH9feQ=SKsuB-zoXthxdMj5GtKw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I need to run 2 million queries against a three columns table t
(s,p,o) which size is 10 billions rows. The data type of each column
is string. The server has 512G RAM, 32 cores and 14T SSD (RAID 0)

Only two types of queries:

select s p o from t where s = param
select s p o from t where o = param

If I store the table in a Postgresql database takes 6 hours using a
Java ThreadPoolExecutor.

Do you think Postgresql itself can speed up the queries processing
even more? What would be the best strategy?

These are my ideas:

1. Use Spark to launch queries against the table (extracted from
Postgresql) loaded in a dataframe
2. Use PG-Strom, an extension module of PostgreSQL with GPU support
and launch the queries against the table.

Any suggestion will be appreciated

Best,

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2020-07-15 15:28:18 Re: 2 million queries against a table
Previous Message Tom Lane 2020-07-15 14:58:27 Re: single table - fighting a seq scan