[firebird-support] Problem with sub-optimal query plan
I have a database containing(simplifyinga little) a CUSTOMERS table andan INVOICES table. INVOICES contains a foreign key to CUSTOMERS, as you can expect. I want to retrieve rows showing INVOICES data and some CUSTOMERS data so I use an inner join, and I want to see newer invoices first so I add an ORDER BY clause. I only need a few rowssince I will show only the latest invoices in a grid. The query is something like SELECT INVOICES.*, CUSTOMERS.NAME FROM INVOICES INNER JOIN CUSTOMERS ON INVOICES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC I have created a descending index on INVOICES.YEAR, INVOICES.NUMBER The problem is that if the tables are large the query is rather slow. Looking at the plan I see: PLAN SORT (JOIN (CUSTOMERS NATURAL, INVOICES INDEX (FK_INVOICES_CUSTOMERS))) so the database loads all rows and then it sorts them.Of course it is slow. If I test a query like SELECT * FROM INVOICES ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC the plan is PLAN (INVOICESORDER IDX_INVOICES_DESC) and the query is much faster since it uses the index and it only reads a few rows. So it looks like if there is an inner join the optimizer does not use the descending index on INVOICES to speed up things. Is there a way to force the optimizer to use the descending index instead of sorting all the resulting rows? I am using Firebird 2.1 on Windows. Thanks in advance. Fulvio Senore ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem with sub-optimal query plan
You could try an outer join ... mucky in the extreme but it sometimes forces the optimiser to look at the sane table first ... (yes I know one shouldn't have to do that sort of thing). On 03/02/2015 12:17, Fulvio Senore mail...@fsoft.it [firebird-support] wrote: I have a database containing(simplifyinga little) a CUSTOMERS table andan INVOICES table. INVOICES contains a foreign key to CUSTOMERS, as you can expect. I want to retrieve rows showing INVOICES data and some CUSTOMERS data so I use an inner join, and I want to see newer invoices first so I add an ORDER BY clause. I only need a few rowssince I will show only the latest invoices in a grid. The query is something like SELECT INVOICES.*, CUSTOMERS.NAME FROM INVOICES INNER JOIN CUSTOMERS ON INVOICES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC I have created a descending index on INVOICES.YEAR, INVOICES.NUMBER The problem is that if the tables are large the query is rather slow. Looking at the plan I see: PLAN SORT (JOIN (CUSTOMERS NATURAL, INVOICES INDEX (FK_INVOICES_CUSTOMERS))) so the database loads all rows and then it sorts them.Of course it is slow. If I test a query like SELECT * FROM INVOICES ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC the plan is PLAN (INVOICESORDER IDX_INVOICES_DESC) and the query is much faster since it uses the index and it only reads a few rows. So it looks like if there is an inner join the optimizer does not use the descending index on INVOICES to speed up things. Is there a way to force the optimizer to use the descending index instead of sorting all the resulting rows? I am using Firebird 2.1 on Windows. Thanks in advance. Fulvio Senore -- Tim Ward ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem with sub-optimal query plan
On Feb 3, 2015, at 8:17 AM, Fulvio Senore mail...@fsoft.it [firebird-support] firebird-support@yahoogroups.com wrote: I want to retrieve rows showing INVOICES data and some CUSTOMERS data so I use an inner join, and I want to see newer invoices first so I add an ORDER BY clause. I only need a few rowssince I will show only the latest invoices in a grid. The query is something like SELECT INVOICES.*, CUSTOMERS.NAME FROM INVOICES INNER JOIN CUSTOMERS ON INVOICES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC I have created a descending index on INVOICES.YEAR, INVOICES.NUMBER The problem is that if the tables are large the query is rather slow. Looking at the plan I see: PLAN SORT (JOIN (CUSTOMERS NATURAL, INVOICES INDEX (FK_INVOICES_CUSTOMERS))) so the database loads all rows and then it sorts them.Of course it is slow. Unfortunately, there's nothing in your query that limits the number of invoices you return for each customer. If you actually wanted all the invoices, the sort would be faster than the random retrieval by invoice index. But you don't. And what you want is not the first customer/invoice pair, but all customers and only the first invoice from each. You might try something like this: select c.name, (select first 1 i.* from invoices i where i.customer_id = c.customer_id order by i.year desc, i.number descending) from customers c If you have customers without invoices, you could add a where exists ... to the end of the query. Good luck, Ann
