CODEWITHSUNDEEP
sqlpostgresqlperformance
rakadiyas
rakadiyas

Reputation: 19

Make query run faster - IT HAS NO JOIN

I got a really huge amount of data that are used to be joined anywhere just to get it (because it was really slow the team decided to gather it all into one table), but now even though they're literally right in one table (no join needed).

It's still so slow. Taking a one day range filter event will lead to time out (took more than 10s, yes that's how bad it is).

What should I suggest to my DBA?

Upvotes: 0

Views: 81

Answers (2)

Terry Carmen
Terry Carmen

Reputation: 3896

As others have said, you need an index. However if it's really huge you can partition the data.

This allows you to drop sections of the data without using time consuming deletes. For example if you're working with some sort of historical data and want to keep 3 months worth, you can partition by month, then each month drop the oldest partition.

However on a more general note, it's rarely a good idea to take a slow multi-table query and glom it all together to improve performance. What you really need is to figure out what's wrong with the slow query and fix it.

This is a job for your DBA.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48865

What is the "selectivity"? That is, how many rows does your select expect to retrieve? 100% of the rows? 1% of the rows? 0.01% of the rows?

1. Low selectivity

If the selectivity is low (i.e less than 5%, ideally less than 0.5%) then good indexing is the best practice.

If so, which columns in the where clause (filtering columns) have the best (lowest) selectivity? Add these columns first in the index.

Once you have decided on the best index, you can make the table a "clustered index" table using that index. That way the heap will be presorted (fast lookup) by the index columns, for improved io since the disk blocks will be looked up sequentially.

2. High selectivity

If the selectivity is high (20% or more), there's no much you can do on your side (development). You could still get some improvement by:

  • Removing unneeded columns.
  • Make sure the select uses a FULL TABLE SCAN.
  • Ask the DBA to assign more resources (SGA, disk priority, paralellism, etc.)

3. Otherwise

The amount of data you have vastly exceeds the database resources you have. There's nothing you can do about it, except to tell the client about this reality, and:

  • Find together a way of defining smaller queries that can be achievable.

4. Finally

If you don't understanf the terms of selectivity, full table scan, indexing, database resources, heap, disk blocks, I would recommend you study them. I'm fairly sure you need to fully understand them right now!

Upvotes: 1

Related Questions