Materialized Views

Materialized views are a powerful feature in Tacnode that can dramatically improve query performance by pre-computing and storing the results of complex queries. Unlike standard views that execute their underlying queries each time they're accessed, materialized views physically store query results, providing immediate access to pre-calculated data.

Understanding Materialized Views

A materialized view is a database object that contains the results of a query. Think of it as a "snapshot" of data that's computed once and stored physically on disk, similar to a table. However, unlike regular tables, materialized views are defined by a query and can be refreshed to reflect changes in the underlying data.

Key Characteristics:

Performance Benefits:

-- Example: Complex analytical query that benefits from materialization
-- Without materialized view: This query runs every time
SELECT 
    p.category,
    p.brand,
    DATE_TRUNC('month', o.order_date) as month,
    COUNT(DISTINCT o.customer_id) as unique_customers,
    SUM(oi.quantity * oi.unit_price) as total_revenue,
    AVG(oi.quantity * oi.unit_price) as avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2023-01-01'
GROUP BY p.category, p.brand, DATE_TRUNC('month', o.order_date);
 
-- With materialized view: Results are pre-computed and instantly available
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    p.category,
    p.brand,
    DATE_TRUNC('month', o.order_date) as month,
    COUNT(DISTINCT o.customer_id) as unique_customers,
    SUM(oi.quantity * oi.unit_price) as total_revenue,
    AVG(oi.quantity * oi.unit_price) as avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2023-01-01'
GROUP BY p.category, p.brand, DATE_TRUNC('month', o.order_date);
 
-- Query the materialized view (much faster)
SELECT * FROM monthly_sales_summary 
WHERE category = 'Electronics' 
ORDER BY month DESC;

Strategic Use Cases for Materialized Views

Query Performance Acceleration:

Materialized views excel at improving response times for computationally expensive queries, particularly those involving complex aggregations, multiple table joins, or large dataset scans.

Ideal Scenarios:

• Dashboard and Reporting: Pre-compute metrics for business intelligence dashboards
• Data Warehouse Analytics: Store pre-aggregated fact tables for faster OLAP queries
• Complex Aggregations: Cache results of statistical calculations, rolling averages, or trend analyses
• Frequently Accessed Reports: Store results of recurring reports that don't need real-time data

Performance Impact:

-- Example: Sales performance dashboard
-- Before: Complex query taking 15+ seconds
-- After: Materialized view query completing in <100ms
 
CREATE MATERIALIZED VIEW sales_performance_dashboard AS
SELECT 
    DATE_TRUNC('week', sale_date) as week,
    region,
    product_line,
    COUNT(*) as transaction_count,
    SUM(amount) as total_sales,
    AVG(amount) as avg_transaction,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(amount) / COUNT(DISTINCT customer_id) as revenue_per_customer
FROM sales_transactions st
JOIN products p ON st.product_id = p.id
JOIN customers c ON st.customer_id = c.id
WHERE sale_date >= CURRENT_DATE - INTERVAL '52 weeks'
GROUP BY DATE_TRUNC('week', sale_date), region, product_line;

Cross-Data Source Query Optimization:

When queries span multiple data sources or involve expensive remote data access, materialized views can significantly reduce data transfer costs and improve response times.

Common Applications:

• Data Federation: Combine data from multiple databases or external APIs
• Cross-Region Queries: Cache data from geographically distributed sources
• External API Integration: Store API responses to reduce API call frequency and costs
• Hybrid Cloud Architectures: Optimize queries across on-premises and cloud data sources

Example Implementation:

-- Materialized view combining local and external data
CREATE MATERIALIZED VIEW customer_360_view AS
SELECT 
    c.customer_id,
    c.name,
    c.email,
    -- Local transaction data
    COUNT(o.order_id) as total_orders,
    SUM(o.order_value) as lifetime_value,
    MAX(o.order_date) as last_order_date,
    -- External CRM data (via foreign table)
    crm.lead_score,
    crm.customer_segment,
    crm.acquisition_channel,
    -- External support data
    sup.total_tickets,
    sup.satisfaction_score
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN external_crm_data crm ON c.customer_id = crm.customer_id
LEFT JOIN external_support_data sup ON c.customer_id = sup.customer_id
GROUP BY c.customer_id, c.name, c.email, crm.lead_score, 
         crm.customer_segment, crm.acquisition_channel, 
         sup.total_tickets, sup.satisfaction_score;

System Load Reduction:

In high-concurrency environments, materialized views can dramatically reduce system load by eliminating the need to repeatedly execute resource-intensive queries.

Benefits:

• CPU Conservation: Reduce computational load on database servers
• Memory Efficiency: Lower working memory requirements for query execution
• I/O Optimization: Minimize disk reads from large base tables
• Concurrent User Support: Handle more simultaneous users without performance degradation

Real-world Example:

-- High-traffic e-commerce product catalog
CREATE MATERIALIZED VIEW product_catalog_optimized AS
SELECT 
    p.product_id,
    p.name,
    p.description,
    p.base_price,
    -- Pre-computed aggregations
    COALESCE(inv.available_quantity, 0) as stock_level,
    COALESCE(rev.avg_rating, 0) as average_rating,
    COALESCE(rev.review_count, 0) as total_reviews,
    COALESCE(sales.units_sold_30d, 0) as recent_sales,
    -- Category and brand information
    cat.category_name,
    cat.category_path,
    b.brand_name
FROM products p
LEFT JOIN inventory inv ON p.product_id = inv.product_id
LEFT JOIN (
    SELECT product_id, AVG(rating) as avg_rating, COUNT(*) as review_count
    FROM reviews 
    GROUP BY product_id
) rev ON p.product_id = rev.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) as units_sold_30d
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY product_id
) sales ON p.product_id = sales.product_id
JOIN categories cat ON p.category_id = cat.category_id
JOIN brands b ON p.brand_id = b.brand_id
WHERE p.active = true;
 
-- Fast product listing queries
SELECT * FROM product_catalog_optimized 
WHERE category_name = 'Electronics' 
AND stock_level > 0 
ORDER BY average_rating DESC, recent_sales DESC
LIMIT 20;

Data Warehousing and ETL:

Materialized views serve as an excellent tool for building efficient data processing pipelines and maintaining hierarchical data transformations in data warehouses.

ETL Applications:

• Staging Area Management: Create intermediate processing stages
• Data Quality Checks: Store validation results and data profiling metrics
• Incremental Processing: Build efficient change-detection mechanisms
• Data Lineage: Track data transformation stages and dependencies

Multi-tier Processing Example:

-- Tier 1: Raw data cleansing and standardization
CREATE MATERIALIZED VIEW cleaned_transaction_data AS
SELECT 
    transaction_id,
    UPPER(TRIM(customer_name)) as customer_name,
    CASE 
        WHEN amount < 0 THEN ABS(amount) 
        ELSE amount 
    END as normalized_amount,
    DATE(transaction_timestamp) as transaction_date,
    COALESCE(product_category, 'Unknown') as product_category
FROM raw_transactions
WHERE transaction_timestamp IS NOT NULL
AND amount IS NOT NULL;
 
-- Tier 2: Business logic application
CREATE MATERIALIZED VIEW business_ready_transactions AS
SELECT 
    *,
    CASE 
        WHEN normalized_amount > 1000 THEN 'High Value'
        WHEN normalized_amount > 100 THEN 'Medium Value'
        ELSE 'Low Value'
    END as value_segment,
    EXTRACT(QUARTER FROM transaction_date) as transaction_quarter
FROM cleaned_transaction_data;
 
-- Tier 3: Analytical aggregations
CREATE MATERIALIZED VIEW quarterly_business_metrics AS
SELECT 
    transaction_quarter,
    product_category,
    value_segment,
    COUNT(*) as transaction_count,
    SUM(normalized_amount) as total_revenue,
    AVG(normalized_amount) as avg_transaction_value
FROM business_ready_transactions
GROUP BY transaction_quarter, product_category, value_segment;

Characteristics of Materialized Views

Materialized views offer several key advantages:

• Pre-computed Results: Query results are calculated once and stored physically, eliminating the need to re-execute complex queries
• Table-like Access: Can be queried using standard SQL just like regular tables, with full index support
• Automatic Synchronization: Data consistency is maintained through controlled refresh mechanisms
• Resource Efficiency: Reduces CPU and I/O overhead by avoiding repeated complex calculations
• Caching Layer: Acts as a high-performance data cache, reducing load on source tables

How to Use Materialized Views

Materialized views can be accessed in two ways:

1. Direct Querying

• Query materialized views like regular tables using standard SQL
• Create indexes on materialized views for enhanced performance
• Note: INSERT, UPDATE, DELETE operations are not allowed
• Data updates require REFRESH commands

2. Automatic Query Optimization

• The optimizer automatically rewrites queries to use materialized views when beneficial
• This optimization only occurs when the materialized view is considered "fresh"

Freshness Rules:

• Views are "fresh" when created and after each refresh
• Views become "expired" when underlying tables change

Key Trade-off:

• Automatic optimization: Convenient but may have unstable performance with expired views
• Direct querying: More predictable performance but data may not be real-time

Categories of Materialized Views

When underlying data changes, materialized views must be updated through a refresh process. Based on refresh strategies, there are two main types:

Materialized views can be categorized based on their refresh strategies into two types:

1. Full Materialized View: In this approach, the materialized view is refreshed by re-executing the query statement in its definition, entirely replacing the existing content with the latest results from the query.

2. Incremental Materialized View: This refresh method analyzes the materialized view's defined query statement, assesses changes since the last update, and merges those changes into the existing materialized view.

Incremental and real-time materialized views require analysis of the query statement and a transformation into a format suitable for incremental calculation. However, due to the intricacies of incremental calculations, not all SQL functions are fully compatible, imposing certain limitations. We will also compare these materialized views across the following dimensions:

VOLATILE functions return results that may change with every call, even when given the same input parameters. Typical examples include:

• Functions returning the current time: clock_timestamp() etc.
• Random number generators: random(), setseed() etc.
• Sequence number functions: nextval(), setval(), lastval() etc.
• Functions related to environment variables: current_query(), timezone(), timeofday() etc.

To get the full list of VOLATILE functions, query

SELECT proname, provolatile FROM pg_proc WHERE provolatile = 'v';

These functions are not supported in incremental materialized views.

In real-world situations, we must decide which materialized views to use based on queries' complexity, frequency, timeliness needs, and a thorough assessment of the associated refresh costs.

Fully Materialized View Management

Creating Fully Materialized Views:

Syntax

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name
	[ (column_name [, ...] ) ] [USING [ ROW | COLUMNAR | HYBRID ]]
	AS query
	[ WITH [ NO ] DATA ];

Parameter description:

• IF NOT EXISTS: No error will be reported if the view exists.
• view_name: Name of the materialized view.
• column_name: Optional; specify the column's name in the view.
• USING: Optional; specify the storage type.
• query: SELECT statement defining the view's content.
• WITH [ NO ] DATA: Indicates whether to populate data during creation.

Example

-- Base Table
CREATE TABLE order_details
(
    order_id     INT,
    product_id   INT,
    product_name VARCHAR(255),
    quantity     INT,
    price        DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);
 
-- Create a materialized view
CREATE MATERIALIZED VIEW sales_mv AS
SELECT product_name, SUM(quantity) AS total_quantity
FROM order_details
GROUP BY product_name;
 
-- Create a normal view
CREATE VIEW sales_v AS
SELECT product_name, SUM(quantity) AS total_quantity
FROM order_details
GROUP BY product_name;
 
-- Create a materialized view based on a normal View
CREATE MATERIALIZED VIEW sales_mv2 AS
SELECT *
FROM sales_v;
 
-- Querying Materialized View Data
CREATE MATERIALIZED VIEW sales_mv3 (upper_name, quantity) AS
SELECT UPPER(product_name), SUM(quantity) AS total_quantity
FROM order_details
GROUP BY product_name;
 
-- querying materialized view D
data
SELECT * FROM sales_mv;
 
-- write test data
INSERT INTO order_details (order_id, product_id, product_name, quantity, price)
VALUES
    (1, 1, 'Product A', 10, 9.99),
    (1, 2, 'Product B', 5, 19.99),
    (2, 1, 'Product A', 20, 9.99),
    (2, 3, 'Product C', 15, 14.99),
    (3, 2, 'Product B', 8, 19.99),
    (3, 3, 'Product C', 12, 14.99),
    (4, 1, 'Product A', 30, 9.99),
    (4, 2, 'Product B', 10, 19.99);
 
-- refresh Materialized View
REFRESH MATERIALIZED VIEW sales_mv;
 
-- Query the Materialized View data again
SELECT * FROM sales_mv;

When creating a materialized view, using WITH DATA (the default) immediately executes a query to populate the data. If the data volume is significant, this process may take considerable time and block other operations. Alternatively, you might opt for WITH NO DATA, which allows you to refresh and fill out the data separately later.

Refreshing:

Syntax:

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] view_name [ WITH [ NO ] DATA ];

Parameter description:

• CONCURRENTLY: This parameter is optional. It allows simultaneous viewing refresh to mitigate the obstruction of other query operations; however, certain restrictions apply:
  • This operation cannot be executed within a transaction block.
  • The WITH NO DATA option is unsupported.
• view_name: Denotes the name of the materialized view designated for refreshment.
• WITH [ NO ] DATA:
  • WITH DATA (Default): This option refreshes the data immediately using the query statement outlined by the view.
  • WITH NO DATA: This option updates only the view's definition without refreshing the data. It's commonly used when creating a new view, which is then complemented by a separate data refresh.

Example

--Normal refresh, this statement will immediately execute the query defined in the view and update the data in mv_sales. The table will not be locked during the refresh.
REFRESH MATERIALIZED VIEW sales_mv;
 
--Concurrent refresh, this statement will perform refresh operations in parallel in the background, and will not block queries on the view. However, the view may still return old data during the refresh.
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_mv;

Note:

1. Updating materialized views uses resources and requires balancing the frequency of refreshes with the need for real-time data.

2. It’s advisable to schedule refreshes during off-peak times or to implement an automated scheduling system for updates.

Deleting:

Syntax

DROP MATERIALIZED VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];

Parameter Details

• IF EXISTS: No error will be raised if the specified view doesn’t exist.
• view_name: This represents the name of the materialized view intended for deletion. You can specify multiple values separated by commas.
• CASCADE: This option removes any dependent objects associated with the view, like indexes.
• RESTRICT: If there are dependent objects, the deletion will not proceed—this is the default behavior.

-- Normal delete, this statement will delete the materialized view mv_sales. If other objects depend on this view, the delete operation will be aborted, and an error will be reported.
DROP MATERIALIZED VIEW sales_mv;
 
-- Cascading delete, this statement not only deletes the view sales_mv, but also deletes all objects that depend on it, such as indexes.
DROP MATERIALIZED VIEW sales_mv CASCADE;
 
-- Using IF EXISTS, this statement will delete the view sales_mv. If the view does not exist, no error will be reported.
DROP MATERIALIZED VIEW IF EXISTS sales_mv;

1. Deleting a materialized view is permanent and cannot be undone, so please take care when proceeding.

2. Before deletion, confirm that the view is no longer necessary and that no dependent objects exist. Check the view's dependencies to prevent accidental deletion.

3. When using CASCADE, exercise caution, as all dependent objects will be deleted simultaneously, potentially causing significant consequences.

4. You must have the necessary permissions to delete a view. Insufficient permissions will result in an error.

5. Deleting a view does not remove data from the source table; it only deletes the precomputed and stored results.

6. Regularly review and delete materialized views that are no longer used to conserve storage space.

7. Maintain a reasonable number of materialized views, as an excessive amount can raise maintenance expenses and complicate the system.

Incremental Materialized View Management

Syntax

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name
	[ (column_name [, ...] ) ] [USING [ ROW | COLUMNAR | HYBRID ]]
	WITH (refresh_mode='incremental') AS query
	[ WITH [ NO ] DATA ];;

Parameter Explanation:

• refresh_mode: Refresh mode, incremental indicates incremental refresh mode.
• For other parameter meanings, refer to the definition of a full materialized view.

Example:

-- Base detail table: order_details
CREATE TABLE order_details
(
    order_id     INT,
    product_id   INT,
    product_name VARCHAR(255),
    quantity     INT,
    price        DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);
 
-- Create an incremental materialized view
CREATE MATERIALIZED VIEW sales_mv WITH (refresh_mode='incremental') AS
SELECT product_name, SUM(quantity) AS total_quantity
FROM order_details
GROUP BY product_name;
 
-- Query data from the Materialized View
SELECT * FROM sales_mv;
 
-- Insert sample data
INSERT INTO order_details (order_id, product_id, product_name, quantity, price)
VALUES
    (1, 1, 'Product A', 10, 9.99),
    (1, 2, 'Product B', 5, 19.99),
    (2, 1, 'Product A', 20, 9.99),
    (2, 3, 'Product C', 15, 14.99),
    (3, 2, 'Product B', 8, 19.99),
    (3, 3, 'Product C', 12, 14.99),
    (4, 1, 'Product A', 30, 9.99),
    (4, 2, 'Product B', 10, 19.99);
 
-- Refresh data in the Materialized View
REFRESH MATERIALIZED VIEW sales_mv;
 
-- Query data from the Materialized View again
SELECT * FROM sales_mv;

Refreshing Incremental Views:

Syntax:

REFRESH MATERIALIZED VIEW view_name;

Parameter Explanation:

• view_name: The name of the materialized view to refresh.

Example:

-- Incremental refresh, this statement immediately executes the defined query of the view and updates `sales_mv` incrementally. No table locking occurs during the refreshing.
REFRESH MATERIALIZED VIEW sales_mv;

Notes:

1. Refreshing a materialized view is resource-intensive, so it is essential to balance the refresh frequency and real-time requirements.
2. Consider executing scheduled refreshes during non-peak business hours or using a scheduler for automated refreshing.

Modifying Incremental Materialized View Definition:

TacNode supports partial modification of incremental materialized view definitions, allowing you to add or remove columns in the final result set. After modifying the materialized view definition, a refresh will trigger a full refresh of the view.

Syntax:

ALTER MATERIALIZED VIEW [ IF EXISTS ] view_name AS query;

Example:

ALTER MATERIALIZED VIEW order_payment_summary AS
SELECT
    o.order_id,
    o.user_id,
    o.order_time,
    p.payment_id,
    p.payment_time,
    p.payment_method,
    p.payment_status,
    CASE
        WHEN p.payment_status = 'success' THEN 'paid'
        WHEN p.payment_status = 'failed' THEN 'payment_failed'
        ELSE 'awaiting_payment'
    END AS payment_status_category
FROM
    orders o
LEFT JOIN
    payments p ON o.order_id = p.order_id;

Important Notes:

If the modification scope exceeds adding or removing columns scenarios, it may trigger structural incompatibility of the materialized view. During updates, you may see the error "unsupported feature: alter primary key while altering query", indicating that this definition change is not supported.

Deleting Incremental Views:

DROP MATERIALIZED VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];

Parameter Explanation:

• Same meanings as full materialized views.

Altering Storage:

Syntax:

ALTER MATERIALIZED VIEW [ IF EXISTS ] view_name SET ACCESS METHOD [ ROW | COLUMNAR | HYBRID ];

Creating Indexes:

Syntax:

CREATE INDEX CONCURRENTLY index_name ON MV_NAME (column_name);

Materialized View Metadata

TacNode provides the pg_matviews system view for querying metadata of materialized views created in the database. This allows you to efficiently manage materialized views, including querying, monitoring, and optimizing their performance. In practical scenarios, you can use pg_matviews to perform the following common management tasks:

Listing All Materialized Views

If you need to see which materialized views exist in the current database, you can simply query pg_matviews:

SELECT matviewname AS view_name, schemaname AS schema
FROM pg_matviews;

This allows you to quickly locate all materialized views and their schemas, providing information for further management.

Viewing Materialized View Definitions

When you need to identify the underlying query of a materialized view, you can query the definition column in pg_matviews:

SELECT matviewname AS view_name, definition
FROM pg_matviews
WHERE matviewname = 'target_view';

This is very useful for understanding the underlying logic of materialized views, debugging, or optimization.

Checking if a Materialized View is Populated

By checking the ispopulated field, you can determine whether a materialized view was populated with data when created:

SELECT matviewname AS view_name, ispopulated
FROM pg_matviews
WHERE matviewname = 'target_view';

If ispopulated is false, it means the view is not populated and you need to manually execute REFRESH MATERIALIZED VIEW.

Reviewing and Optimizing Materialized Views

By combining metadata, you can optimize storage and performance:

• Avoid using OUTER JOIN, try to use INNER JOIN instead
• Review overly nested SQL in definitions and optimize query structure
• Use indexes to improve materialized view query performance

For example, for frequently queried materialized views, add indexes to speed up queries:

CREATE INDEX idx_target_view ON target_view (column_name);

For more materialized view commands, refer to CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, and DROP MATERIALIZED VIEW documentation.

Materialized View Permissions

In TacNode, the permission system for materialized views maintains complete compatibility with PostgreSQL, supporting standard object access control, role and permission inheritance, GRANT/REVOKE authorization, ownership management, etc. The main rules are as follows:

Permission Overview

Permission Details

1. Object ownership: The owner of a materialized view has all operation permissions (including REFRESH, ALTER, DROP, etc.), and ownership can be transferred via ALTER MATERIALIZED VIEW ... OWNER TO ....
2. Authorization and revocation: You can use GRANT/REVOKE to authorize or revoke SELECT, ALTER, DROP and other permissions, with syntax completely consistent with PostgreSQL.
3. Roles and inheritance: Supports role inheritance and group roles, where permissions can be passed through role chains.
4. Access control: All permission checks remain consistent with PostgreSQL, supporting fine-grained object-level authorization.
5. Refresh permissions: Only the materialized view owner can execute REFRESH, which cannot be granted to other users via GRANT. If multiple users need refresh capability, this can be achieved through ownership transfer, using SECURITY DEFINER functions, or role switching.

Common Authorization Operation Examples

-- Grant user1 query permission
GRANT SELECT ON MATERIALIZED VIEW user_order_summary TO user1;
 
-- Grant user1 modify definition permission
GRANT ALTER ON MATERIALIZED VIEW user_order_summary TO user1;
 
-- Grant user1 delete permission
GRANT DROP ON MATERIALIZED VIEW user_order_summary TO user1;
 
-- Revoke user1's query permission
REVOKE SELECT ON MATERIALIZED VIEW user_order_summary FROM user1;
 
-- Transfer ownership
ALTER MATERIALIZED VIEW user_order_summary OWNER TO user1;

Materialized Views and Indexes

As a type of physically stored table, materialized views fully support index creation and usage, which can significantly improve query performance:

Index Advantages

• Query acceleration: Creating indexes on materialized views can significantly improve query performance, especially for point queries and range queries
• Sorting optimization: Indexes can help optimize ORDER BY operations, avoiding additional sorting overhead
• Join optimization: Creating indexes on join keys can optimize joins between materialized views and other tables

Index Creation Examples

-- Create a secondary index on the user_id column of the materialized view
CREATE INDEX idx_user_order_summary_user_id ON user_order_summary (user_id);
 
-- Create an index on the region column of the materialized view to optimize region-based queries
CREATE INDEX idx_user_order_summary_region ON user_order_summary (region);
 
-- Create a composite index to optimize complex queries
CREATE INDEX idx_user_order_summary_vip_region ON user_order_summary (vip_level, region);

Best Practices:

• Create indexes on columns frequently used in WHERE conditions, JOIN conditions, and ORDER BY clauses
• Regularly review index usage and remove unused indexes
• For materialized views that are frequently queried but not frequently refreshed, you can create more indexes to optimize query performance