Re: Query performance help with 'shadow table' approach.

On 13 Sep 2011, at 23:44, Brian Fehrle wrote:

> These queries basically do a 'select max(primary_key_column) from table group by column1, column2." Because of the group by, we would result in a sequential scan of the entire table which proves to be costly.

That seems to suggest a row where the primary key that has the max value is "special" in some way. Making them more easily distinguishable from "normal" rows seems like a good idea here.

> Since the table has a ton of columns, I set up a smaller table that will house a copy of some of the data that the query uses, the Primary Key colum, and the two columns I do my 'group by' on.

That's one way to distinguish these special rows from the rest. You could also mark them as special using an extra column and/or create an expression-based index over just those rows.

However, especially with the below section in mind, it would appear your data could be normalised a bit more (your splitting off that shadow table is a step in doing so, in fact).

I'm also wondering, does your primary key have actual meaning? It would appear to just indicate the order in which the records were created (I'm assuming it's a serial type surrogate PK, and not a natural one).

> This shadow table will also only contain one row for every column1 and column2 combination (due to the group by), and for those rows, will have the max of the primary key. Even with this, the 'shadow' table will have about 14 million rows, compared to the 15 million in the main table.

Don't (column1, column2) make up a key then? I get the feeling you should split your table in 3 sections:
Table 1: main lookup (PK: pkey_sid)
Table 2: Variation lookup (PK: (column1, column2), FK: pkey_sid)
Table 3: Data (FK: the above)

> So the issue here comes in retrieving the needed data from my main table. The resulting rows is estimated to be 409,600, and the retrieving of the primary key's that are associated with those rows is actually really easy. However, when we take those 409,600 rows back to the main table to retrieve the other columns I need, the planner is just doing a sequential scan as it's most likely going to be faster than hitting the index then retrieving the columns I need for all 400K+ rows.

Is that estimate accurate? If not, see Ondrej's suggestions.

That is only about 1/30th of your table. I don't think a seqscan makes sense here unless your data is distributed badly.

> Things to note:
> 1. If I reduce my where clause's range, then the sequential scan turns into an index scan, but sadly this can't always be done.

Does it make sense to CLUSTER your data in some sense? That would improve the data distribution issue and would probably push the threshold for a seqscan up some.

Cheers,

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.