[Info-ingres] Comparing QEPs

[Paul White]

Hi Steve,

 

Use HELP TABLE to make sure the statistics are recorded against the columns
in the SELECT and WHERE clause.

sql db1 <<eof > db1.txt

help table tablename \g\q

eof

 

sql db2 <<eof > db2.txt

help table tablename \g\q

eof

 

diff db1.txt db2.txt

 

Double check all tables and indexes from the query are configured the same:
column data type, page size, storage, primary keys, foreign key constraints.

 

Run optimizedb -zc -zk dbname -rtablename 

Run statdump dbname -rtablename

 

Does the QEP show a timeout?

Compare the configuration of the servers: 

degree_of_parallelism

opf_hash_aggregate

opf_hash_join 

opf_greedy_factor

opf_joinop_timeout

qflatten_aggregate

qflatten_singleton

 

Perhaps send a copy of the select and QEPs.  

 

Paul

&

 

-----Original Message-----
From: info-ingres-bounces at lists.planetingres.org
[mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of
s.anderson.au at gmail.com
Sent: Thursday, 15 August 2019 10:48 AM
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] Comparing QEPs

 

Hi there

 

Had an issue where a query in one ABF screen started running very slowly,
instead of completing instantly, it now takes twenty seconds.

 

Re-generating the statistics for the associated tables did not help.

 

As such I copied the tables to our dev box and compared the query plans
Ingres created on dev vs production. On production the QEP did not utilise a
secondary index, whereas on dev it did.

 

The dev vs production is not a perfection comparison as in the process of
copying the tables across to dev, the tables were rebuilt.

 

I tried to force Ingres to use the secondary index by tweaking the query to
explicitly join with the secondary index, but my hack did not help.

 

What is the best approach for this situation? Should I be rebuilding the
tables involved?

 

Thanks

Steve

_______________________________________________

Info-ingres mailing list

Info-ingres at lists.planetingres.org

https://lists.planetingres.org/mailman/listinfo/info-ingres

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20190815/94f82212/attachment.html>