0

I have a Citus 9.5 database (postgres 11) on Azure and executing a complicated query with CTEs.

The schema has a simple view from another schema as:

create view my_schema.table1
as
select * from other_schema.table1

When my query joins to my_schema.table1 it does a Seq Scan on the table which leads to poor performance.

However when my query joins directly to other_schema.table1 it uses the primary key index which is fast.

I have run ANALYZE [table] on both the table and the view.

Why does Postgres not use the index when I query via the view, but it does when I query via the table?

4
  • Please always disclose the version of Postgres (or Citus?) you are working with. Commented Dec 14, 2021 at 0:02
  • Please consider reading this advice
    – mustaccio
    Commented Dec 14, 2021 at 0:37
  • That won't happen with regular PostgreSQL unless the view is security_barrier = on. Must be some modification of Citus. Commented Dec 14, 2021 at 3:10
  • An EXPLAIN (ANALYZE, VERBOSE, BUFFERS) on your two queries would be very helpful - without these, we are flying blind!
    – Vérace
    Commented Dec 14, 2021 at 11:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.