Re: [Firebird-test] [FB-Tracker] Commented: (CORE-3127) somewhere after 2.0.5 - apparently 2.0.6 a significant change was made to the optimizer - I had a query that ran in 2 seconds take 40

[Ray H. <rh...@ro...>]

Below are the timings. Sorry Sean, nice try but not a real big win. I
saved about .5 seconds using EXISTS. PS as you can see below the
original 2.0.5 query takes .15 seconds compared to 31 seconds under
2.1.3 - THIS IS A HUGE PERFORMANCE DEGRADATION. While I have both
installed on my machine, I run 2.0.5.


timings orig query 2.0.5 with fix
 PLAN (PO INDEX (INDX_PAON2))
 PLAN JOIN (PT NATURAL, P NATURAL)

 real	0m0.157s
 user	0m0.114s
 sys	0m0.028s

timings orig query 2.1.3 WITH fix
 PLAN (PO INDEX (INDX_PAON2))
 PLAN JOIN (PT INDEX (FIX_BUG1), P NATURAL)

 real	0m0.489s
 user	0m0.097s
 sys	0m0.053s

timings orig query 2.1.3 without fix (added index)
PLAN (PO INDEX (INDX_PAON2))
PLAN JOIN (P NATURAL, PT NATURAL)

 real	0m31.276s
 user	0m28.578s
 sys	0m2.642s

timings with 2.1.3 EXISTS query without fix
 PLAN (PO INDEX (INDX_PAON2))
 PLAN JOIN (P NATURAL, PT NATURAL)

 real	0m30.758s
 user	0m27.999s
 sys	0m2.611s

:=[ Ray

On Tue, 2010-09-07 at 22:17 +0000, Sean Leyne (JIRA) wrote:
> [ http://tracker.firebirdsql.org/browse/CORE-3127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21684#action_21684 ] 
> 
> Sean Leyne commented on CORE-3127:
> ----------------------------------
> 
> What happens if you try:
> 
> SELECT 
>   p.first_name, p.last_name, pt.patient_id, pt.billto_id, pt.species_id, pt.color, pt.owner_label, pt.deact_code
> FROM patients pt, people p
> WHERE
>   pt.office_id = 1 
>   and pt.deact_code is null 
>   and pt.soundex_name like 'M24%'
>   and (
>     pt.billto_id = p.person_id 
>     or EXISTS (select 1 from patient_owners po where po.patient_id = pt.patient_id and po.person_id = pt.billto_id)
>   )
> ;
> 
> 
> > somewhere after 2.0.5 - apparently 2.0.6 a significant change was made to the optimizer - I had a query that ran in 2 seconds take 40
> > -------------------------------------------------------------------------------------------------------------------------------------
> >
> >                 Key: CORE-3127
> >                 URL: http://tracker.firebirdsql.org/browse/CORE-3127
> >             Project: Firebird Core
> >          Issue Type: Bug
> >          Components: Engine
> >         Environment: Reproducible with isql - tested in 2.0.6 and 2.1.3 - on MAC, Linux and PC platforms
> >            Reporter: Ray Holme
> >         Attachments: addindex.ddl, out.txt, test_xx, vetAdmin.gbak.gz, vetAdmin.so
> >
> >
> > I have a DB, copy of the Linux UDF (can be eliminated as computed fields not referenced in query), copy of the query, timings under 2 releases of Linux (first seen on PC, then on MAC, but confirmed here on  Linux), and an add Index statement that works around the problem for us.
>