Hi all
I have a table, and a very simple view for that table. Sometimes the view
can be complex (having WHERE or GROUP BY statements), but sometimes it just
reflects the table exactly as it is (this depends on user input).
What I find is that, even when the view reflects the original table
exactly, the performance is much slower for the view (with a big number of
rows). After running EXPLAIN, it seems that when querying through the view,
all rows are scanned.
This is the view definition:
CREATE OR REPLACE VIEW RESULTS0 AS select
"a" "a",
"b" "b",
"c" "c"
from RESULTS
limit 1000000
This is the EXPLAIN output for the query against the view:
SELECT
"a",
"b",
"c"
FROM PUBLIC.RESULTS0
/* SELECT
"a" AS "a",
"b" AS "b",
"c" AS "c"
FROM PUBLIC.RESULTS
/++ PUBLIC.RESULTS.tableScan ++/
/++ scanCount: 152612 ++/
LIMIT 1000000
*/
ORDER BY 3
LIMIT 30
And this is the EXPLAIN output for the query against the table:
SELECT
"a",
"b",
"c"
FROM PUBLIC.RESULTS
/* PUBLIC.RESULTS_INDEX */
ORDER BY 3
LIMIT 30
/* index sorted */
When querying the view there is that scanCount: 179110 that suggests that
it is going through all the events.
It is as if it was not using the existing index for the column "c", but if
I remove the ordering at all, I still get the same problem. Same if I
incorporate the order by into the view definition:
SELECT
"a",
"b",
"c"
FROM PUBLIC.RESULTS0
/* SELECT
"a" AS "a",
"b" AS "b",
"c" AS "c"
FROM PUBLIC.RESULTS
/++ PUBLIC.RESULTS_INDEX ++/
/++ scanCount: 160001 ++/
ORDER BY 3
LIMIT 1000000
/++ index sorted ++/
*/
ORDER BY 5
LIMIT 30
Any ideas on how can I get queries against this view to run as fast (or
similar) as queries against the table?
(Note that my goal is not to have view as simple as this... the definition
of the view is controlled by the user interaction, and sometimes the user
has no additions to the original table. Also, I assume that if this is
slower for the simplest case, it will be slower as well for more complex
cases).
thanks
Siro
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
