Question
STAIDLOGIC LLC
CA
Last activity: 30 Jul 2019 20:22 EDT
Database Performance Related query
1. A performance analysis for an equals comparisons- only query against a table shows that it always performs a full table scan. The table has grown steadily over time. Nothing has been done in the database concerning this table other than it being generated by pega.
What do you recommend to address that table size does not impact performance?
Pegasystems Inc.
US
Have you used EXPLAIN against a database query before?
If you have a Personal Edition you have access to pgAdmin4. For "host" you can use 127.0.0.1 and for "port" most likely the default 5432 or look it up in tomcat/conf/context.xml. User/pass should be the default postgres/postgres but if not see tomcat/conf/context.xml.
If you have access to a PostgreSQL database on linux VM, which you should, a simple way to run pgAdmin4 is to install docker, then install a pgAdmin4 docker image, then run it following the directions here: https://hub.docker.com/r/dpage/pgadmin4/
Launch the query tool and enter a query such as:
PE: select pyErrorMessage from data.pc_assign_workbasket
VM: select pyErrorMessage from pegadata.pc_assign_workbasket
Now put "EXPLAIN" in front of the query.
Unless you have a large number of rows you will likely see "Seq scan" (sequential scan) which means the entire table was scanned.
What would you do to address that?
STAIDLOGIC LLC
CA
I would expose few columns within this table and probably add indexes to this table.
Can you please provide your thought?
Pegasystems Inc.
US
"Just a few columns" or every column returned by the query?
How many rows are returned?
What columns would be best to index?
STAIDLOGIC LLC
CA
I would expose all the columns return by the query.
For the indexing part not sure what would be the best.
Your thought please...
Pegasystems Inc.
US
This type of information can be found on the internet.
EX: https://stackoverflow.com/questions/212264/how-to-choose-and-optimize-oracle-indexes
Indexing columns used in joins and frequently used within search criteria makes sense.
The above also states: Do not use standard B-tree indexes on keys or expressions with few distinct values
In general you would not index a boolean column because you would only end up with 2 buckets.
However if you are frequently wanting to find one of the boolean values, and that value is very rare (needle in a haystack), then a DB index on that column could make sense.
According to Information Theory, the smaller the probability that something occurs, the more significant its information. Just the opposite, information content approaches zero when something is virtually certain to occur.
STAIDLOGIC LLC
CA
Thanks, Pederson for posting this Information. Its very helpful.