Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Dmitry Yemanov]

25.04.2016 10:35, Christian Waldmann wrote:

> In Firebird CORE-659 (http://tracker.firebirdsql.org/browse/CORE-659) there
> is a collection of requests for spatial index, e.g. R*Tree or GiST, for GIS
> applications and other. Spatial indexes are also fine to find events in a
> interval of time.
>
> R* Tree at https://en.wikipedia.org/wiki/R*_tree
> GiST at http://gist.cs.berkeley.edu/

Unfortunately, we don't have resources for this task. Only if some 3rd 
party is willing to develop the code and contribute it. I know that 
RedSoft is interested, but I don't know what is their roadmap on this.

> I think the support of spatial indexes can bring a lot of new users from
> SQLite (https://www.sqlite.org/rtree.html ) and PostgreSQL
> (http://www.postgresql.org/docs/8.1/static/indexes-types.html) to FireBird.

Probably for SQLite, unlikely for PGSQL.


Dmitry


--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Ann Harrison]

On Mon, Apr 11, 2016 at 7:40 AM, Dimitry Sibiryakov 
wrote:

> 11.04.2016 13:28, Dmitry Yemanov wrote:
> > But it can be made possible. The question is whether it's worth it.
>
>While bug with orphan index nodes is living in the engine - index only
> scan is
> impossible completely.
>

Without transaction information in the index no purely indexed based scan
is possible.  Orphans don't make any difference.  It doesn't matter whether
the record isn't there or has had its key value  changed by a transaction
that's visible to the current transaction.

   Transformation numeric->double can lose data.


Err.  Not necessarily and probably there's a work around.  Conversions from
numeric to double are precise up to 56 bits.  For values greater than 56
bits, one could add the last byte of the value to the end of the mangled
double and get full precision.  With that, you could drop the special
indexes for INT64.



> Using integers as a key will disable
> altering of numeric columns. May be it worth considering.
>

Not necessary - Even with dealing with fractional values, decimal of fewer
than 57 bits will convert properly in both directions.  There may be some
slop if you try to compare the values exactly, but as long as the
conversion works a slight incompatibility doesn't matter.

   Transformation string->key by ICU does loose data, no way back.
>

I don't think that matters either.  If your collation is accent or case
insensitive, your lookup will also be case and accent insensitive.  You've
asked to lose that information, so it's loss is of no concern.

Cheers,

Ann
--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Atri Sharma]

>
>
>
>  >
> > This optimization can only be done if constant conditions are in the
> > SQL, so +0 like tricks would not be affected.
>
> I already considered that, it seems easy enough to implement.
>
>
>
>
Worth having?



-- 
Regards,

Atri
*l'apprenant*
--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Atri Sharma]

On Mon, Apr 11, 2016 at 6:15 PM, Dmitry Yemanov  wrote:

> 11.04.2016 14:40, Dimitry Sibiryakov wrote:
> >
> > While bug with orphan index nodes is living in the engine - index only
> scan is
> > impossible completely.
>
> Pure index-only scan - maybe. It's impossible without transaction
> numbers in indices too. But there may be other alternatives to consider.
>
> > Transformation numeric->double can loose data. Using integers as a key
> will disable
> > altering of numeric columns. May be it worth considering.
>
> Perhaps we could live with a requirement to rebuild an index for
> integer<->floating modifications. But I'd like to preserve "opaque"
> conversions between different NUMERICs.
>
>
+1


-- 
Regards,

Atri
*l'apprenant*
--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Dmitry Yemanov]

11.04.2016 14:40, Dimitry Sibiryakov wrote:
>
> While bug with orphan index nodes is living in the engine - index only scan is
> impossible completely.

Pure index-only scan - maybe. It's impossible without transaction 
numbers in indices too. But there may be other alternatives to consider.

> Transformation numeric->double can loose data. Using integers as a key will 
> disable
> altering of numeric columns. May be it worth considering.

Perhaps we could live with a requirement to rebuild an index for 
integer<->floating modifications. But I'd like to preserve "opaque" 
conversions between different NUMERICs.

> Transformation string->key by ICU does loose data, no way back.

True for case- and accent-insensitive collations, but probably not 
always true for simple cases. Anyway, I care less about strings than 
about integers and dates/times.


Dmitry


--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Dimitry Sibiryakov]

11.04.2016 13:28, Dmitry Yemanov wrote:
> But it can be made possible. The question is whether it's worth it.

   While bug with orphan index nodes is living in the engine - index only scan 
is 
impossible completely.
   Transformation numeric->double can loose data. Using integers as a key will 
disable 
altering of numeric columns. May be it worth considering.
   Transformation string->key by ICU does loose data, no way back.
   Date/time is fine.

-- 
   WBR, SD.

--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Dmitry Yemanov]

11.04.2016 14:16, Dimitry Sibiryakov wrote:

> 11.04.2016 13:08, Molnár Attila wrote:
>> It's hard and costly (or even possible) to deconstruct the index format to 
>> get the
>> original column values?
>
> Yes, in common case it is impossible.

But it can be made possible. The question is whether it's worth it.


Dmitry


--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Dmitry Yemanov]

11.04.2016 14:08, Molnár Attila wrote:
>
>> When InterBase was created, there was a lot of academic work on
>> optimizing corner cases, with the result that academic databases
>> tended to spend more time optimizing than retrieving.  We made the
>> deliberate choice not to spend optimizer time saving idiots from
>> themselves. Thirty years later, maybe we'd choose differently.
>> However, lots of programs depend on tricks like +0 and concatenating
>> with an empty string to coerce unnatural but effective plans.  I'd
>> worry about the damage done to those cases.
 >
> This optimization can only be done if constant conditions are in the
> SQL, so +0 like tricks would not be affected.

I already considered that, it seems easy enough to implement.


Dmitry


--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Dimitry Sibiryakov]

11.04.2016 13:08, Molnár Attila wrote:
> It's hard and costly (or even possible) to deconstruct the index format to 
> get the
> original column values?

   Yes, in common case it is impossible.

-- 
   WBR, SD.

--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Molnár Attila]

  
  


On 2016.04.09. 20:25, Ann Harrison
  wrote:


  

  On Fri, Apr 8, 2016 at 5:54 AM,
Molnár Attila 
wrote:

  
  
  
Optimizations
    - IS NOT NULL should use index. It is equivalent
with >= min_value or <= max_value based on index
direction
  



 Histograms and clustered indexes (if they're being
  considered) could help here to detect cases where IS NOT
  NULL returns a small subset of the records in a table.  In
  general, searches that touch more than half the records in
  a table are more efficient when made in storage (natural)
  order rather than through an index.  Remember that
  Firebird stored data and indexes separately, so setting up
  an indexed retrieval that will touch every page in a table
  is just overhead compared with straight-forwardly reading
  every page.
  

  

I ment min_value and max_value not base on table data, but on column
data type.

  

  



       - condition
pre-evaluation and reduction. e.g.: WHERE 1 = 2 AND
field = :param is always FALSE. Evaluation does not
needed for all records, can decide at prepare time
whether the result is an empty result set or an
unfiltered result set.
  



When InterBase was created, there was a lot of academic
  work on optimizing corner cases, with the result that
  academic databases tended to spend more time optimizing
  than retrieving.  We made the deliberate choice not to
  spend optimizer time saving idiots from themselves. 
  Thirty years later, maybe we'd choose differently. 
  However, lots of programs depend on tricks like +0 and
  concatenating with an empty string to coerce unnatural but
  effective plans.  I'd worry about the damage done to those
  cases.
  

  

This optimization can only be done if constant conditions are in the
SQL, so +0 like tricks would not be affected.

  

  
 


       - use index in
"NATURAL" mode when column in a conditional appears in a
multi column index, but not in the first place. You may
reduce number of database page visits in this way :
index page can hold more effective record data because
it's narrower than the table data page record (also in
worst case it could be worse than NATURAL because ot the
mixed index and table data page read, but I think
overall it could worth it, especially in big tables.
measurements needed)
  



I not sure what you mean by "NATURAL" index mode -
  "natural" usually means reading the data pages in storage
  order without any index. If you mean reading across the
  leaf level of the index to find matches in the second and
  subsequent keys in an index, you have no idea how hard
  that would be.  Firebird index keys are mashed up values
  created so they compare bytewise in the desired order. 
  When using an index, Firebird hasn't a clue where the
  boundaries fall between columns in multi-column index. 
  It's just bytes.   The format makes indexes dense and
  comparisons quick.   Changing the key format to support
  partial matches on second and third columns seems like a
  bad idea, given that there's very little difference
  between having an index on each column and a multi-column
  index.  Remember that Firebird uses multiple indexes on a
  single table. 

  

  

I ment the second case (reading across the leaf level). It's hard
and costly (or even possible) to deconstruct the index format to get
the original column values? The question is reading all leaf
deconstruct and filter is faster than reading all records natural an
filter.

  

  


 

       - SELECT
DISTINCT  FROM table is slow
(natural scan on all records) and SELECT  FROM table GROUP BY  is
also slow (worse! : index 

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Ann Harrison]

On Fri, Apr 8, 2016 at 5:54 AM, Molnár Attila  wrote:

>
>
> *O*
> *ptimizations*- IS NOT NULL should use index. It is equivalent with
> >= min_value or <= max_value based on index direction
>

 Histograms and clustered indexes (if they're being considered) could help
here to detect cases where IS NOT NULL returns a small subset of the
records in a table.  In general, searches that touch more than half the
records in a table are more efficient when made in storage (natural) order
rather than through an index.  Remember that Firebird stored data and
indexes separately, so setting up an indexed retrieval that will touch
every page in a table is just overhead compared with straight-forwardly
reading every page.

- condition pre-evaluation and reduction. e.g.: WHERE 1 = 2 AND field =
> :param is always FALSE. Evaluation does not needed for all records, can
> decide at prepare time whether the result is an empty result set or an
> unfiltered result set.
>

When InterBase was created, there was a lot of academic work on optimizing
corner cases, with the result that academic databases tended to spend more
time optimizing than retrieving.  We made the deliberate choice not to
spend optimizer time saving idiots from themselves.  Thirty years later,
maybe we'd choose differently.  However, lots of programs depend on tricks
like +0 and concatenating with an empty string to coerce unnatural but
effective plans.  I'd worry about the damage done to those cases.


> - use index in "NATURAL" mode when column in a conditional appears in
> a multi column index, but not in the first place. You may reduce number of
> database page visits in this way : index page can hold more effective
> record data because it's narrower than the table data page record (also in
> worst case it could be worse than NATURAL because ot the mixed index and
> table data page read, but I think overall it could worth it, especially in
> big tables. measurements needed)
>

I not sure what you mean by "NATURAL" index mode - "natural" usually means
reading the data pages in storage order without any index. If you mean
reading across the leaf level of the index to find matches in the second
and subsequent keys in an index, you have no idea how hard that would be.
Firebird index keys are mashed up values created so they compare bytewise
in the desired order.  When using an index, Firebird hasn't a clue where
the boundaries fall between columns in multi-column index.  It's just
bytes.   The format makes indexes dense and comparisons quick.   Changing
the key format to support partial matches on second and third columns seems
like a bad idea, given that there's very little difference between having
an index on each column and a multi-column index.  Remember that Firebird
uses multiple indexes on a single table.



> - SELECT DISTINCT  FROM table is slow (natural scan on
> all records) and SELECT  FROM table GROUP BY  fields> is also slow (worse! : index scan on all records). I think in this
> case it's not necessary to read all the records in the table, it should be
> enough to read #of distinct  values from table. (currently
> you have to keep a separate table with this information because you can't
> access to this information fast)
>

Unh, no.  Indexes are multi-generational structures, so they often contain
more entries than there are records visible to any one transaction.  At a
minimum, you've got to touch the records that appear good candidates from
the index.

Good luck,

Ann
--
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301=/ca-pub-7940484522588532Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Molnár Attila]

Hi Dmitry!

Hope never dies. I prefer optimizations over the other new features. ;)

Thanks for the reply.


On 2016.04.08. 12:30, Dmitry Yemanov wrote:
> 08.04.2016 12:54, Molnár Attila wrote:
>> Here is my list.
> List of v4 features is already composed. We may add some more
> improvements here and there, but no promises about them.
>
>
> Dmitry
>
>
> --
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Dmitry Yemanov]

08.04.2016 12:54, Molnár Attila wrote:
>
> Here is my list.

List of v4 features is already composed. We may add some more 
improvements here and there, but no promises about them.


Dmitry


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel