FirebirdSQL
Improvement in sorting - "subnode level sorting" to use one index for many query with different order by fields [CORE2795]
Submitted by: @livius2
Votes: 2
Now when we create index for field DATEP in table TEST like:
CREATE TABLE TEST ( DATEP DATE, FLAG1 INTEGER, FLAG2 INTEGER, FLAG3 INTEGER, FLAG4 INTEGER .... ) and you only create index on fields DATEP CREATE INDEX IXA_TEST__DATEP ON TEST(DATEP)
only query like this can use this index
SELECT * FROM TEST WHERE ....ORDER BY DATEP PLAN (ORDER IXA_TEST__DATEP)
but this not use index :-( SELECT * FROM AAA WHERE ....ORDER BY DATAP, FLAG1 SELECT * FROM AAA WHERE ....ORDER BY DATAP, FLAG2 SELECT * FROM AAA WHERE ....ORDER BY DATAP, FLAG3
######################################
my proposition is - creating feature what i call "subnode level sorting"
what i call subnodes? look at result set DataP, FLAG1 '2009-12-08' 15 '2009-12-08' 6 '2009-12-08' 18 '2009-12-09' 894 '2009-12-09' 222 '2009-12-11' 894 '2009-12-11' 222 '2009-12-11' 555 '2009-12-11' 763
first subnode '2009-12-08' 15 '2009-12-08' 6 '2009-12-08' 18
second sub node '2009-12-09' 894 '2009-12-09' 222
third sub node '2009-12-11' 894 '2009-12-11' 222 '2009-12-11' 555 '2009-12-11' 763
when you have result set sorted by IXA_TEST__DATEP you need only to sort "sub nodes" by FLAG1 field not whole result set which i suppose is faster?
In real word is not possible to craete index for all combination of fields in above example one index optimize many query like SELECT * FROM TEST ORDER BY DATEP, FLAG1 SELECT * FROM TEST ORDER BY DATEP, FLAG2
and also any other combination SELECT * FROM TEST ORDER BY DATEP, FLAG2, FLAG1, FLAG3 ...
Modified by: @pcisar
summary: Improvement in sorting - "subnode level sorting" to use index for many query with diddefent order by fields => Improvement in sorting - "subnode level sorting" to use index for many query with different order by fields
Modified by: @pcisar
Component: Engine [ 10000 ]
summary: Improvement in sorting - "subnode level sorting" to use index for many query with different order by fields => Improvement in sorting - "subnode level sorting" to use one index for many query with different order by fields
Modified by: @pcisar
reporter: Pavel Cisar [ pcisar ] => Karol Bieniaszewski [ livius ]
Commented by: @dyemanov
Karol, please don't change the "affected version" field for non-bug tickets. It makes no sense.
I see that postgress have this feature and it name it as "incremental sort" in explain plan https://www.pgmustard.com/docs/explain/incremental-sort https://www.postgresql.org/docs/14/using-explain.html
