| Subject | RE: [firebird-support] Natural Plan when grouping by 5 indexed columns? |
|---|---|
| Author | Leyne, Sean |
| Post date | 2007-02-01T03:09:43Z |
Richard,
complete table.
That being the case, it is always faster to perform a natural scan and
sort rather than walk an index.
Why is it faster?
Because the index will be sorted in value order, and not page order.
This means that while walking the index the engine would cause the HD to
thrash as the engine reads the appropriate data page for each record in
the index.
Why does it read the records?
Because Firebird is a multi-generational engine, which means that the
indexes contain data which may be 'dirty', the engine needs to read the
records to confirm the data values (for this transaction).
- Not multi-generational
- a desktop database (like Paradox), so you're comparing apples and
oranges.
Sean
> I was poking around looking for outliers in our multi-database testType1",
> suite and I found the following Firebird 2 query that was
> significantly slower (the average of a week's worth of nightly runs)
> than the other 10 SQL engines I test (including three Microsoft Jet
> versions):
>
> 2007-01-31 15:31:38.122: <QUERY>
> 2007-01-31 15:31:38.122: SELECT "Staples"."Prod Type1" AS "Prod
> 2007-01-31 15:31:38.122: "Staples"."Prod Type2" AS "Prod Type2",sec.
> 2007-01-31 15:31:38.122: "Staples"."Prod Type3" AS "Prod Type3",
> 2007-01-31 15:31:38.122: "Staples"."Prod Type4" AS "Prod Type4",
> 2007-01-31 15:31:38.122: "Staples"."Product Name" AS "Product Name"
> 2007-01-31 15:31:38.122: FROM "Staples"
> 2007-01-31 15:31:38.122: GROUP BY 1,
> 2007-01-31 15:31:38.122: 2,
> 2007-01-31 15:31:38.122: 3,
> 2007-01-31 15:31:38.122: 4,
> 2007-01-31 15:31:38.122: 5
> 2007-01-31 15:31:38.122: </QUERY>
> 2007-01-31 15:31:38.137: <PLAN>
> 2007-01-31 15:31:38.137: PLAN SORT ((Staples NATURAL))
> 2007-01-31 15:31:38.137: </PLAN>
> 2007-01-31 15:31:43.090: [Time] Getting the records took 1.7665
> 2007-01-31 15:31:43.106: [Count] Query returned 1932 records.Since you haven't specified any WHERE criteria, you are asking for the
>
> The thing that struck me as odd was that all 5 columns are indexed
> and there is no aggregation, yet the optimiser decided to use a
> natural ordering.
complete table.
That being the case, it is always faster to perform a natural scan and
sort rather than walk an index.
Why is it faster?
Because the index will be sorted in value order, and not page order.
This means that while walking the index the engine would cause the HD to
thrash as the engine reads the appropriate data page for each record in
the index.
Why does it read the records?
Because Firebird is a multi-generational engine, which means that the
indexes contain data which may be 'dirty', the engine needs to read the
records to confirm the data values (for this transaction).
> Is this a case where a compound index would help? It strikes me asThat's because Jet is:
> very strange that FB2 is slower at performing this query than Jet is
> on a 54860 line csv file...
- Not multi-generational
- a desktop database (like Paradox), so you're comparing apples and
oranges.
Sean