Single-table query

This topic shows sample SET EXPLAIN output for a simple query and a complex query on a single table.

Partial SET EXPLAIN output for a simple query shows SET EXPLAIN output for a simple query.
Figure 1: Partial SET EXPLAIN output for a simple query
QUERY:
------
SELECT fname, lname, company FROM customer

Estimated Cost: 2
Estimated # of Rows Returned: 28

  1) virginia.customer: SEQUENTIAL SCAN

Partial SET EXPLAIN output for a complex query shows SET EXPLAIN output for a complex query on the customer table.

Figure 2: Partial SET EXPLAIN output for a complex query
QUERY:
------
SELECT fname, lname, company FROM customer
WHERE company MATCHES 'Sport*' AND
    customer_num BETWEEN 110 AND 115
ORDER BY lname

Estimated Cost: 1
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

  1) virginia.customer: INDEX PATH

        Filters: virginia.customer.company MATCHES 'Sport*'

    (1) Index Keys: customer_num   (Serial, fragments: ALL)
        Lower Index Filter: virginia.customer.customer_num >= 110
        Upper Index Filter: virginia.customer.customer_num <= 115
The following output lines in Partial SET EXPLAIN output for a complex query show the scope of the index scan for the second query:
  • Lower Index Filter: virginia.customer.customer_num >= 110

    Start the index scan with the index key value of 110.

  • Upper Index Filter: virginia.customer.customer_num <= 115

    Stop the index scan with the index key value of 115.