[jira] Updated: (DERBY-1506) full table scans of tables which don't use indexes, which have blobs, but don't reference blob data still read all pages of the table

["Mike Matrigali (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-1506?page=all ]

Mike Matrigali updated DERBY-1506:
----------------------------------

    Description: 
A full table scan that does not use an index always reads every allocated page of the table through the cache.  In two cases 
this means logically it reads more data that necessary: long rows (where the head columns span multiple pages) and long columns
(columns where the data in a single column spans multiple pages).  In both these cases the space for the "overflow" portion of the
row or column is currently stored in the same space as the regular "main" pages.  The current implementation of a table scan of 
a heap container is to call raw store to give it a linear list of main pages with rows, raw store conglomerate implementations step through each allocated page in the container and returns the "main" pages (reading the overflow pages into cache, identifying them, and skipping them) 
the access layer which then returns rows as requested to the query processing layer.

If a table contains rows with very long columns (ie. 2gig blobs), and the tablescan does not request the blob data then a lot of data
is read from disk but not required by the query. 
A more unusual case is a table scan on requiring a few columns from a table made up of  2 gig rows made up of all less than 32k columns.,
in this case also derby  will read all pages as part of a tablescan even if only the first column is the only required column of the chain.

Note that this is only a problem in tablescan of heap tables.  In both cases if an index is used to get the row, then ONLY the required data is
read from disk.  In the long column case the main row has only a pointer to the overflow chain for the blob and it will not be read unless the
blob data is required.  In the long row case data, columns appear in the container in the order they are created in the original "create table"
statement.  Data is read from disk into cache for all columns from the 1st up to the "last"  one referenced in the query.  Data objects are only
instantiated from the cache data for the columns referenced in the query.

I have marked this low in priority as I believe that full, unindexed tables scans of tables with gigabyte blobs are not the normal case.  Seems like most applications would do keyed lookups of the table.    But there may be apps that need to
do full table reporting on the non'-blob data in such a table.

  was:
A full table scan that does not use an index always reads every allocated page of the table through the cache.  In two cases 
this means logically it reads more data that necessary: long rows (where the head columns span multiple pages) and long columns
(columns where the data in a single column spans multiple pages).  In both these cases the space for the "overflow" portion of the
row or column is currently stored in the same space as the regular "main" pages.  The current implementation of a table scan of 
a heap container is to call raw store to give it a linear list of main pages with rows, raw store conglomerate implementations step through each allocated page in the container and returns the "main" pages (reading the overflow pages into cache, identifying them, and skipping them) 
the access layer which then returns rows as requested to the query processing layer.

If a table contains rows with very long columns (ie. 2gig blobs), and the tablescan does not request the blob data then a lot of data
is read from disk but not required by the query. 
A more unusual case is a table scan on requiring a few columns from a table made up of  2 gig rows made up of all less than 32k columns.,
in this case also derby  will read all pages as part of a tablescan even if only the first column is the only required column of the chain.

Note that this is only a problem in tablescan of heap tables.  In both cases if an index is used to get the row, then ONLY the required data is
read from disk.  In the long column case the main row has only a pointer to the overflow chain for the blob and it will not be read unless the
blob data is required.  In the long row case data, columns appear in the container in the order they are created in the original "create table"
statement.  Data is read from disk into cache for all columns from the 1st up to the "last"  one referenced in the query.  Data objects are only
instantiated from the cache data for the columns referenced in the query.


This improvement report was prompted by the following performance report on the derby list:
Hi all,

When experimenting with BLOB's I ran into a performance issue
that I cannot completely explain, but it could be a bug.

Given the following table:

CREATE TABLE BLOB_TABLE (
BLOB_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
BLOB_SIZE BIGINT NOT NULL,
BLOB_CRC BIGINT NOT NULL,
BLOB_NAME VARCHAR(255) NOT NULL,
BLOB_DATA BLOB(2G) NOT NULL,
UNIQUE (BLOB_CRC, BLOB_SIZE),
PRIMARY KEY (BLOB_ID)
);

which is populated with 27 rows,
where the sum of all BLOB sizes is 5,885,060,164 bytes
(about 200 MB average per BLOB, but ranging from 10 MB to 750 MB).

Some queries on this table are executed really
fast (almost instantaneous response).

However, the following query needs about 10 minutes to complete:

SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE;

I reasoned that maybe Derby is scanning the whole table
(including the blob contents) so I tried to add a dummy WHERE
clause (dummy because all BLOB_ID's are greater than 0)
to offer a clue as to what rows (all of course) are needed,
as follows

SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE WHERE BLOB_ID > 0;

and that helped: instantaneous response.

But I really think that the original query,
without the where clause, should not be any slower.


I am using Derby 10.1.3.1 embedded, Windows XP and Sun Java 1.5.0_06.
Both queries executed with a Statement, not a PreparedStatement.

Kind regards,

Piet Blok


> full table scans of tables which don't use indexes, which have blobs, but don't reference blob data still read all pages of the table
> -------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-1506
>          URL: http://issues.apache.org/jira/browse/DERBY-1506
>      Project: Derby
>         Type: Improvement

>   Components: Store
>     Versions: 10.1.3.1
>     Reporter: Mike Matrigali
>     Priority: Minor

>
> A full table scan that does not use an index always reads every allocated page of the table through the cache.  In two cases 
> this means logically it reads more data that necessary: long rows (where the head columns span multiple pages) and long columns
> (columns where the data in a single column spans multiple pages).  In both these cases the space for the "overflow" portion of the
> row or column is currently stored in the same space as the regular "main" pages.  The current implementation of a table scan of 
> a heap container is to call raw store to give it a linear list of main pages with rows, raw store conglomerate implementations step through each allocated page in the container and returns the "main" pages (reading the overflow pages into cache, identifying them, and skipping them) 
> the access layer which then returns rows as requested to the query processing layer.
> If a table contains rows with very long columns (ie. 2gig blobs), and the tablescan does not request the blob data then a lot of data
> is read from disk but not required by the query. 
> A more unusual case is a table scan on requiring a few columns from a table made up of  2 gig rows made up of all less than 32k columns.,
> in this case also derby  will read all pages as part of a tablescan even if only the first column is the only required column of the chain.
> Note that this is only a problem in tablescan of heap tables.  In both cases if an index is used to get the row, then ONLY the required data is
> read from disk.  In the long column case the main row has only a pointer to the overflow chain for the blob and it will not be read unless the
> blob data is required.  In the long row case data, columns appear in the container in the order they are created in the original "create table"
> statement.  Data is read from disk into cache for all columns from the 1st up to the "last"  one referenced in the query.  Data objects are only
> instantiated from the cache data for the columns referenced in the query.
> I have marked this low in priority as I believe that full, unindexed tables scans of tables with gigabyte blobs are not the normal case.  Seems like most applications would do keyed lookups of the table.    But there may be apps that need to
> do full table reporting on the non'-blob data in such a table.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira