sql: virtual tables don't respect LIMIT when there is a WHERE clause

[rafiss]

To reproduce

It might relate to the indexes that were added to vtables.

root@:26257/defaultdb> create table t (id int primary key, name text, notselected text, ts timestamp with time zone, intarr int[]);
CREATE TABLE

Time: 74.401ms

root@:26257/defaultdb> select oid from pg_class where relname = 't';;
  oid
-------
  800
(1 row)

Time: 54.675ms

root@:26257/defaultdb> select attname from pg_attribute limit 1;
     attname
-----------------
  descriptor_id
(1 row)

Time: 52.461ms

root@:26257/defaultdb> select attname from pg_attribute where attrelid = 800 limit 1;
    attname
---------------
  id
  name
  notselected
  ts
  intarr
(5 rows)

On v20.2

[jordanlewis]

Hm, yeah, looks like the optimizer thinks this can be at most 1 row so it removes the limit. Not sure why.

[email protected]:57327/defaultdb> explain(opt,verbose) select attname from pg_attribute where attrelid = 800 limit 5;
                         text
-------------------------------------------------------
  project
   ├── columns: attname:3
   ├── cardinality: [0 - 1]
   ├── stats: [rows=1]
   ├── cost: 1.29
   ├── key: ()
   ├── fd: ()-->(3)
   ├── prune: (3)
   └── scan pg_attribute@pg_attribute_attrelid_idx
        ├── columns: attrelid:2 attname:3
        ├── constraint: /2: [/800 - /800]
        ├── cardinality: [0 - 1]
        ├── stats: [rows=1, distinct(2)=1, null(2)=0]
        ├── cost: 1.27
        ├── key: ()
        └── fd: ()-->(2,3)
(16 rows)

[rohany]

Is the index advertised as unique?

[jordanlewis]

No