Re: need suggestion on querying big tables

Hi Ebin,

If you can share your tables' definitions along with index informations on
them and your query with execution plan, it would lead more fruitful
discussions.

In addition to what folks have already told, I recommend you to take into
account other columns too, such as the ones in where clause, order by
clause, and returned by the query. Adding those columns into column list of
index or having them in include definition may help.

Another hint is about the expressions you use in the query. You can create
an index by using expressions. It will help PostgreSQL not to calculate
them while executing the query.

Regarding to indexes, you can consider using partitial indexes. If some
portion of data is not relevant with your query, you can eliminate in
advance. And, you will have smaller index with better performance. If it is
small enough to fit in the memory, it is even better.

Another significant topic about indexes is type of index. Multiple indexing
method and many opclases are available in Postgresql out of box. Depending
on characteristic of your data and your query, the solution geting results
lightning fast can be hiding there.

Last but not least is vacuum. People already mentioned statistics of your
table. What about vacuum? When have you vacuumed those tables last time?

If you want to go even deeper, columns's storage settings can create a
significant difference for some edge cases.

I haven't even started talking about partitioning, check constraints,
constraint exclusions, partition pruning, jit, or other kind of hacks, such
as offset 0 :)

Best regards.
Samed YILDIRIM

On Mon, 5 Dec 2022, 08:30 Ebin Jozer, <ebinjozer(at)gmail(dot)com> wrote:

> Hi Team,
> in postgresql 11 version we have two tables of size 435 GB and 347 GB.
> if we query on single table or if we are doing inner join on both the big
> tables, it is not displacing any output, it keeps running
>
> We can see the wait event is IO and directDatafile .
>
> Server Spec : 8 cores and 64GB RAM
> PG config : 53 GB(effective_cache), 12 GB(shared buffer)
>
> can you please suggest some ideas , how we can query on big tables and
> fasten them to get the output??
>
> Thanks & Regards,
> *Ebin*
>