Single-table query

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

Figure 1 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

Figure 2 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 Figure 2 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.