colttaylor
Programmer
BACKSTORY (skip this if you are in a hurry)
I have an in-house application which uses PDAC to access a database of about 10 tables. When the total number of rows per table was small, performance was great! Now things seem to be slowing down. After reading a few technical papers at pervasive.com, I know that the problem is my failure to include enough indeces in the original table designs.
I have been using the transactional access to pervasive for years, but didn't think about indeces when I started my first relational access application.
I am now planning to add indeces to each table's creation sql script, then create new tables and move the data over from the old tables. I expect to see some significant improvements. If not, I will have to recode the entire application using the transactional api.
QUESTION (please read this even if you are in a hurry)
My question is about the indeces which I am going to add...
Given a table called TABLE1 which contains three fields called FLD1, FLD2 and FLD3. FLD1 is a manually maintained unique id which already has an index. FLD2 and FLD3 are non-unique fields which need indeces because they are often used in WHERE clauses.
To query-optimize for...
SELECT FLD1,FLD2,FLD3 FROM TABLE1 WHERE FLD2 = "VALUE"
...must the new index be based on FLD2 alone (allowing duplicates) or can it be FLD2;FLD1 (a multi-segment index with no-dups)?
For my transactional access applications, I usually create
all of my indeces to be no-dups by using a unique field by adding a unique field as the last segment. It is sometimes helpful to keep every index key unique so that GET_EQUALS can get the current record, no matter what index I am using.
Is PSQL smart enough to use a multi-segment index to optimize a query whose where clause only uses the first segment's field?
Also, If I use compound where clauses like "...where FLD2="NEW" and FLD3="GREEN"", should I add multi-segment indeces containing both fields?
Thanks for any help!
If it's stupid but it works, it isn't stupid
I have an in-house application which uses PDAC to access a database of about 10 tables. When the total number of rows per table was small, performance was great! Now things seem to be slowing down. After reading a few technical papers at pervasive.com, I know that the problem is my failure to include enough indeces in the original table designs.
I have been using the transactional access to pervasive for years, but didn't think about indeces when I started my first relational access application.
I am now planning to add indeces to each table's creation sql script, then create new tables and move the data over from the old tables. I expect to see some significant improvements. If not, I will have to recode the entire application using the transactional api.
QUESTION (please read this even if you are in a hurry)
My question is about the indeces which I am going to add...
Given a table called TABLE1 which contains three fields called FLD1, FLD2 and FLD3. FLD1 is a manually maintained unique id which already has an index. FLD2 and FLD3 are non-unique fields which need indeces because they are often used in WHERE clauses.
To query-optimize for...
SELECT FLD1,FLD2,FLD3 FROM TABLE1 WHERE FLD2 = "VALUE"
...must the new index be based on FLD2 alone (allowing duplicates) or can it be FLD2;FLD1 (a multi-segment index with no-dups)?
For my transactional access applications, I usually create
all of my indeces to be no-dups by using a unique field by adding a unique field as the last segment. It is sometimes helpful to keep every index key unique so that GET_EQUALS can get the current record, no matter what index I am using.
Is PSQL smart enough to use a multi-segment index to optimize a query whose where clause only uses the first segment's field?
Also, If I use compound where clauses like "...where FLD2="NEW" and FLD3="GREEN"", should I add multi-segment indeces containing both fields?
Thanks for any help!
If it's stupid but it works, it isn't stupid
