firebird-test

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...>]

Sean has no problem and neither did I - sorry.

ray@rainbow FB_bug]$ ls
addindex.ddl  out.txt  test_xx  vetAdmin.gbak.gz  vetAdmin.so
[ray@rainbow FB_bug]$ gunzip vetAdmin.gbak.gz 
[ray@rainbow FB_bug]$ gbak -rep -z vetAdmin.gbak test.fdb
gbak:gbak version LI-V2.0.5.13206 Firebird 2.0
gbak:    Version(s) for database "test.fdb"
	Firebird/linux AMD64 (access method), version "LI-V2.0.5.13206 Firebird
2.0"
	on disk structure version 11.0
[ray@rainbow FB_bug]$ ls -l
total 21308
-rw-r--r--. 1 ray staff      146 2010-09-07 17:33 addindex.ddl
-rw-r--r--. 1 ray staff     1077 2010-09-07 17:33 out.txt
-rw-rw----. 1 ray staff 16545792 2010-09-08 06:56 test.fdb
-rw-r--r--. 1 ray staff      513 2010-09-07 17:32 test_xx
-rw-r--r--. 1 ray staff  5242368 2010-09-07 17:33 vetAdmin.gbak
-rw-r--r--. 1 ray staff    14816 2010-09-07 17:33 vetAdmin.so




On Wed, 2010-09-08 at 03:28 +0000, Dmitry Yemanov (JIRA) wrote:
> [ http://tracker.firebirdsql.org/browse/CORE-3127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21688#action_21688 ] 
> 
> Dmitry Yemanov commented on CORE-3127:
> --------------------------------------
> 
> The attached backup fails to restore with v2.0.5:
> 
> unsuccessful metadata update.
>      TABLE INVOICE_LINES.
>      Can't have relation with only computed fields or constraints.
> 
> 
> > 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.
> 

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.
> 

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...>]

On Tue, 2010-09-07 at 22:23 +0000, Sean Leyne (JIRA) wrote:
> The original SQL seems to be missing the relationship between patients
> and people.  How are the tables related?

Sean,

If you are wondering why I do NOT use foreign keys - I DO NOT.

Instead I use trigger code and procedures to verify the foreign key.

  a) foreign key indices may not be dropped for a hard load and on the
secondary table(s) - they can be a stupid waste (see below)

  b) foreign key indices sometimes confuse the optimizer into
     using then when it would be a VERY bad idea
     e.g. if you had a large DB of USA people with a foreign
          key on state, and referenced state as a requirement
          in the query - you would have been better off with
          a natural run

I have been burned before MANY TIMES and have been using Interbase since
release 2.0. I specialize in performance and times where things in a DB
are sometimes slow.

Ray

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...>]

On Wed, 2010-09-08 at 06:58 -0400, Ray Holme wrote:
> Sean has no problem and neither did I - sorry.

Great, sorry that I left the healing index in the DB.

drop index fix_bug1;

will make things work like they did without a workaround.

:=]