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
Samed YILDIRIM
On Mon, 5 Dec 2022, 08:30 Ebin Jozer, <ebinjozer@xxxxxxxxx> 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 runningWe can see the wait event is IO and directDatafile .Server Spec : 8 cores and 64GB RAMPG 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
