Lucid Indexes

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Lucid Indexes

Tom Barber
Just quickie,

I run this sql:

explain plan for SELECT TRAD_CODE, CURR_PROD_CODE
FROM pdi.f_movement
WHERE TRAD_CODE='A09' AND
CURR_PROD_CODE='12' AND
MV_DATE< DATE '2009-08-20'

On my database I have index 4 which is trad_code and curr_prod_code and I also have index5 which is trad_code, curr_prod_code and mv_date, why does lucid want to use index4 over index5?

Cheers

Tom


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Lucid Indexes

Tom Barber
And this:

explain plan for Select
f_inventory.trad_code as "trad_code",
f_inventory.prod_code as "prod_code",
f_inventory.average_cost as "average_cost",
f_inventory.on_hand as "on_hand"
FROM pdi.f_inventory

WHERE stockdate = DATE '2009-08-21'

ORDER BY prod_code

returns     LcsRowScanRel(table=[[LOCALDB, PDI, F_INVENTORY]], projection=[[0, 1, 4, 5]], clustered indexes=[[SYS$CLUSTERED_INDEX$F_INVENTORY$AVERAGE_COST, SYS$CLUSTERED_INDEX$F_INVENTORY$ON_HAND, SYS$CLUSTERED_INDEX$F_INVENTORY$PROD_CODE, SYS$CLUSTERED_INDEX$F_INVENTORY$TRAD_CODE]])

So would I be correct in assuming indexes don't work on date columns? :)

Cheers

Tom




On Mon 24/08/09 11:00 AM , [hidden email] sent:
Just quickie,

I run this sql:

explain plan for SELECT TRAD_CODE, CURR_PROD_CODE
FROM pdi.f_movement
WHERE TRAD_CODE='A09' AND
CURR_PROD_CODE='12' AND
MV_DATE< DATE '2009-08-20'

On my database I have index 4 which is trad_code and curr_prod_code and I also have index5 which is trad_code, curr_prod_code and mv_date, why does lucid want to use index4 over index5?

Cheers

Tom



------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Lucid Indexes

John Sichi
Administrator
No, there's nothing special about dates.  Here's an example of a plan
which uses an index on them:

0: jdbc:luciddb:> explain plan for select * from s.t where d=date
'1990-10-03';
'column0'
'FennelToIteratorConverter'
'  LcsRowScanRel(table=[[LOCALDB, S, T]], projection=[*], clustered
indexes=[[SYS$CLUSTERED_INDEX$T$D, SYS$CLUSTERED_INDEX$T$I]])'
'    LcsIndexSearchRel(table=[[LOCALDB, S, T]], index=[XD],
projection=[*], inputKeyProj=[[1, 3]], inputDirectiveProj=[[0, 2]],
startRidParamId=[0], rowLimitParamId=[0])'
'      FennelValuesRel(tuples=[[{ '[', 1990-10-03, ']', 1990-10-03 }]])'

JVS

[hidden email] wrote:

> And this:
>
> explain plan for Select
> f_inventory.trad_code as "trad_code",
> f_inventory.prod_code as "prod_code",
> f_inventory.average_cost as "average_cost",
> f_inventory.on_hand as "on_hand"
> FROM pdi.f_inventory
>
> WHERE stockdate = DATE '2009-08-21'
>
> ORDER BY prod_code
>
> returns     LcsRowScanRel(table=[[LOCALDB, PDI, F_INVENTORY]],
> projection=[[0, 1, 4, 5]], clustered
> indexes=[[SYS$CLUSTERED_INDEX$F_INVENTORY$AVERAGE_COST,
> SYS$CLUSTERED_INDEX$F_INVENTORY$ON_HAND,
> SYS$CLUSTERED_INDEX$F_INVENTORY$PROD_CODE,
> SYS$CLUSTERED_INDEX$F_INVENTORY$TRAD_CODE]])
>
> So would I be correct in assuming indexes don't work on date columns? :)
>
> Cheers
>
> Tom
>
>
>
> On Mon 24/08/09 11:00 AM , [hidden email] sent:
>
>     Just quickie,
>
>     I run this sql:
>
>     explain plan for SELECT TRAD_CODE, CURR_PROD_CODE
>     FROM pdi.f_movement
>     WHERE TRAD_CODE='A09' AND
>     CURR_PROD_CODE='12' AND
>     MV_DATE< DATE '2009-08-20'
>
>     On my database I have index 4 which is trad_code and curr_prod_code
>     and I also have index5 which is trad_code, curr_prod_code and
>     mv_date, why does lucid want to use index4 over index5?
>
>     Cheers
>
>     Tom
>
>
>
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Lucid Indexes

John Sichi
Administrator
In reply to this post by Tom Barber
I can reproduce the issue by first creating the wider index and
verifying that the optimizer chooses that, and then creating the
narrower prefix index and verifying that the optimizer switches to that.

However, it may be a valid choice by the optimizer, since accessing the
narrower index may incur less I/O if the date portion of the predicate
isn't very selective.

You could try running it with and without index4 and index5 to see which
one actually executes faster, but it may be hard to tell the difference
(or other factors such as caching may interfere).

JVS

[hidden email] wrote:

> Just quickie,
>
> I run this sql:
>
> explain plan for SELECT TRAD_CODE, CURR_PROD_CODE
> FROM pdi.f_movement
> WHERE TRAD_CODE='A09' AND
> CURR_PROD_CODE='12' AND
> MV_DATE< DATE '2009-08-20'
>
> On my database I have index 4 which is trad_code and curr_prod_code and
> I also have index5 which is trad_code, curr_prod_code and mv_date, why
> does lucid want to use index4 over index5?
>
> Cheers
>
> Tom
>
>
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Lucid Indexes

Francisco Reyes-3
In reply to this post by Tom Barber
[hidden email] writes:

> And this:
>
> explain plan for Select

One thing worth checking; were the tables analyzed?

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Lucid Indexes

Jeremy Lemaire
In reply to this post by Tom Barber
After running EXPLAIN PLAN on the following query I noticed that the cost of the LcsIndexSearchRel on the LANGUAGE_DIMENSION DELETION_INDEX was alarmingly high (see line 11 of explain plan output).  

EXPLAIN PLAN INCLUDING ALL ATTRIBUTES WITH IMPLEMENTATION FOR
SELECT SUM("COUNT") as "COUNT", lan."VALUE" AS "LANGUAGE"
FROM ad_inventory_warehouse.ad_inventory_summary_daily_fact f
        INNER JOIN ad_inventory_warehouse.language_dimension lan USING (parameter_set_id)
WHERE lan."VALUE" IN ( 'ENGLISH' ) 
	AND f.DATETIME >= timestamp '2009-12-01 00:00:00' AND f.DATETIME < timestamp '2009-12-02 00:00:00'
GROUP BY lan."VALUE";

FennelToIteratorConverter: rowcount = 2.4193669724425992E11, cumulative cost = 5.564627598534418E12
  FennelReshapeRel(projection=[[1, 0]], outputRowType=[RecordType(INTEGER COUNT, VARCHAR(32) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL LANGUAGE) NOT NULL]): rowcount = 2.4193669724425992E11, cumulative cost = 5.322690901290158E12
    LhxAggRel(groupCount=[1], COUNT=[SUM($1)]): rowcount = 2.4193669724425992E11, cumulative cost = 5.080754204045898E12
      FennelReshapeRel(projection=[[1, 3]], outputRowType=[RecordType(VARCHAR(32) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL LANGUAGE, INTEGER COUNT) NOT NULL]): rowcount = 2.419366972442599E12, cumulative cost = 4.838817506801639E12
        LhxJoinRel(leftKeys=[[2]], rightKeys=[[1]], joinType=[INNER]): rowcount = 2.419366972442599E12, cumulative cost = 2.419450534359039E12
          FennelReshapeRel(projection=[[0, 1, 0]], outputRowType=[RecordType(INTEGER NOT NULL PARAMETER_SET_ID, VARCHAR(32) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL VALUE, INTEGER CAST($0):INTEGER) NOT NULL]): rowcount = 2.7878563005E7, cumulative cost = 7.534218065735099E7
            LcsRowScanRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE, LANGUAGE_DIMENSION]], projection=[[1, 2]], clustered indexes=[[SYS$CLUSTERED_INDEX$LANGUAGE_DIMENSION$PARAMETER_SET_ID, SYS$CLUSTERED_INDEX$LANGUAGE_DIMENSION$VALUE]]): rowcount = 2.7878563005E7, cumulative cost = 4.7463617652350985E7
              LcsIndexMinusRel(startRidParamId=[1], rowLimitParamId=[2]): rowcount = 1.39392815025E7, cumulative cost = 1.226252755075E8
                LcsIndexSearchRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE, LANGUAGE_DIMENSION]], index=[LANGUAGE_VALUE_IDX], projection=[*], inputKeyProj=[[1, 3]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0]): rowcount = 2.7878563005E7, cumulative cost = 2.7878564005E7
                  FennelValuesRel(tuples=[[{ '[', 'ENGLISH', ']', 'ENGLISH' }]]): rowcount = 1.0, cumulative cost = 1.0
                LcsIndexSearchRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE, LANGUAGE_DIMENSION]], index=[SYS$DELETION_INDEX$LANGUAGE_DIMENSION], projection=[*], inputKeyProj=[[1, 3]], inputDirectiveProj=[[0, 2]], startRidParamId=[1], rowLimitParamId=[2]): rowcount = 8.0807429E7, cumulative cost = 8.080743E7
                  FennelValuesRel(tuples=[[{ '[', null, '+', null }]]): rowcount = 1.0, cumulative cost = 1.0
          LcsRowScanRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE, AD_INVENTORY_SUMMARY_DAILY_FACT]], projection=[[1, 3]], clustered indexes=[[SYS$CLUSTERED_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT$DATETIME, SYS$CLUSTERED_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT$COUNT, SYS$CLUSTERED_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT$PARAMETER_SET_ID]], residual columns=[[4]]): rowcount = 578548.9426669726, cumulative cost = 8219735.782774224
            LcsIndexSearchRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE, AD_INVENTORY_SUMMARY_DAILY_FACT]], index=[SYS$DELETION_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT], projection=[*], inputKeyProj=[[1, 3]], inputDirectiveProj=[[0, 2]], startRidParamId=[0], rowLimitParamId=[0]): rowcount = 1.1678192E8, cumulative cost = 1.16781921E8
              FennelValuesRel(tuples=[[{ '-', null, '+', null }]]): rowcount = 1.0, cumulative cost = 1.0
            FennelValuesRel(tuples=[[{ '[', 2009-12-01 00:00:00, ')', 2009-12-02 00:00:00 }]]): rowcount = 1.0, cumulative cost = 1.0

After reading here I did a ALTER TABLE REBUILD followed by an ANALYZE TABLE to try and cleanup.  This had no effect on the cost of this operation.  The EXPLAIN PLAN output remained exactly the same.  

Should the cost of the deletion index operation be a concern and if so is there a way to reduce it?  
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Lucid Indexes

John Sichi
Administrator
Sorry, the cost as reported by explain plan is currently bogus; please
ignore it.  The problem is that we do accurate logical costing early
on during optimization (which is used for things like join ordering
and index selection), but we don't preserve that information all the
way through, and at the end when EXPLAIN PLAN runs, it sees bogus
physical costs (calculated by another method) instead.  There are ways
to get the real costs for logical operators by turning on optimizer
tracing, but I don't recommend it unless you are looking for something
very specific.

Eventually we'll get the real costs exposed via EXPLAIN PLAN.

JVS

On Fri, Mar 26, 2010 at 11:46 AM, haawker <[hidden email]> wrote:

>
> After running EXPLAIN PLAN on the following query I noticed that the cost of
> the LcsIndexSearchRel on the LANGUAGE_DIMENSION DELETION_INDEX was
> alarmingly high (see line 11 of explain plan output).
>
> EXPLAIN PLAN INCLUDING ALL ATTRIBUTES WITH IMPLEMENTATION FOR
> SELECT SUM("COUNT") as "COUNT", lan."VALUE" AS "LANGUAGE"
> FROM ad_inventory_warehouse.ad_inventory_summary_daily_fact f
>        INNER JOIN ad_inventory_warehouse.language_dimension lan USING
> (parameter_set_id)
> WHERE lan."VALUE" IN ( 'ENGLISH' )
>        AND f.DATETIME >= timestamp '2009-12-01 00:00:00' AND f.DATETIME <
> timestamp '2009-12-02 00:00:00'
> GROUP BY lan."VALUE";
>
> FennelToIteratorConverter: rowcount = 2.4193669724425992E11, cumulative cost
> = 5.564627598534418E12
>  FennelReshapeRel(projection=[[1, 0]], outputRowType=[RecordType(INTEGER
> COUNT, VARCHAR(32) CHARACTER SET "ISO-8859-1" COLLATE
> "ISO-8859-1$en_US$primary" NOT NULL LANGUAGE) NOT NULL]): rowcount =
> 2.4193669724425992E11, cumulative cost = 5.322690901290158E12
>    LhxAggRel(groupCount=[1], COUNT=[SUM($1)]): rowcount =
> 2.4193669724425992E11, cumulative cost = 5.080754204045898E12
>      FennelReshapeRel(projection=[[1, 3]],
> outputRowType=[RecordType(VARCHAR(32) CHARACTER SET "ISO-8859-1" COLLATE
> "ISO-8859-1$en_US$primary" NOT NULL LANGUAGE, INTEGER COUNT) NOT NULL]):
> rowcount = 2.419366972442599E12, cumulative cost = 4.838817506801639E12
>        LhxJoinRel(leftKeys=[[2]], rightKeys=[[1]], joinType=[INNER]):
> rowcount = 2.419366972442599E12, cumulative cost = 2.419450534359039E12
>          FennelReshapeRel(projection=[[0, 1, 0]],
> outputRowType=[RecordType(INTEGER NOT NULL PARAMETER_SET_ID, VARCHAR(32)
> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL
> VALUE, INTEGER CAST($0):INTEGER) NOT NULL]): rowcount = 2.7878563005E7,
> cumulative cost = 7.534218065735099E7
>            LcsRowScanRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE,
> LANGUAGE_DIMENSION]], projection=[[1, 2]], clustered
> indexes=[[SYS$CLUSTERED_INDEX$LANGUAGE_DIMENSION$PARAMETER_SET_ID,
> SYS$CLUSTERED_INDEX$LANGUAGE_DIMENSION$VALUE]]): rowcount = 2.7878563005E7,
> cumulative cost = 4.7463617652350985E7
>              LcsIndexMinusRel(startRidParamId=[1], rowLimitParamId=[2]):
> rowcount = 1.39392815025E7, cumulative cost = 1.226252755075E8
>                LcsIndexSearchRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE,
> LANGUAGE_DIMENSION]], index=[LANGUAGE_VALUE_IDX], projection=[*],
> inputKeyProj=[[1, 3]], inputDirectiveProj=[[0, 2]], startRidParamId=[0],
> rowLimitParamId=[0]): rowcount = 2.7878563005E7, cumulative cost =
> 2.7878564005E7
>                  FennelValuesRel(tuples=[[{ '[', 'ENGLISH', ']', 'ENGLISH'
> }]]): rowcount = 1.0, cumulative cost = 1.0
>                LcsIndexSearchRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE,
> LANGUAGE_DIMENSION]], index=[SYS$DELETION_INDEX$LANGUAGE_DIMENSION],
> projection=[*], inputKeyProj=[[1, 3]], inputDirectiveProj=[[0, 2]],
> startRidParamId=[1], rowLimitParamId=[2]): rowcount = 8.0807429E7,
> cumulative cost = 8.080743E7
>                  FennelValuesRel(tuples=[[{ '[', null, '+', null }]]):
> rowcount = 1.0, cumulative cost = 1.0
>          LcsRowScanRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE,
> AD_INVENTORY_SUMMARY_DAILY_FACT]], projection=[[1, 3]], clustered
> indexes=[[SYS$CLUSTERED_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT$DATETIME,
> SYS$CLUSTERED_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT$COUNT,
> SYS$CLUSTERED_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT$PARAMETER_SET_ID]],
> residual columns=[[4]]): rowcount = 578548.9426669726, cumulative cost =
> 8219735.782774224
>            LcsIndexSearchRel(table=[[LOCALDB, AD_INVENTORY_WAREHOUSE,
> AD_INVENTORY_SUMMARY_DAILY_FACT]],
> index=[SYS$DELETION_INDEX$AD_INVENTORY_SUMMARY_DAILY_FACT], projection=[*],
> inputKeyProj=[[1, 3]], inputDirectiveProj=[[0, 2]], startRidParamId=[0],
> rowLimitParamId=[0]): rowcount = 1.1678192E8, cumulative cost = 1.16781921E8
>              FennelValuesRel(tuples=[[{ '-', null, '+', null }]]): rowcount
> = 1.0, cumulative cost = 1.0
>            FennelValuesRel(tuples=[[{ '[', 2009-12-01 00:00:00, ')',
> 2009-12-02 00:00:00 }]]): rowcount = 1.0, cumulative cost = 1.0
>
> After reading
> http://pub.eigenbase.org/wiki/LucidDbDataStorageAndAccess#Table_Rebuild here
> I did a ALTER TABLE REBUILD followed by an ANALYZE TABLE to try and cleanup.
> This had no effect on the cost of this operation.  The EXPLAIN PLAN output
> remained exactly the same.
>
> Should the cost of the deletion index operation be a concern and if so is
> there a way to reduce it?
> --
> View this message in context: http://n2.nabble.com/Lucid-Indexes-tp3502521p4805684.html
> Sent from the luciddb-users mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Download Intel&#174; Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw-dev
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users
>

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...