Postgresql – Creating Synonym For a Table in PostgreSQL over dblink

Here is what I think is happening. I don't think it is a bug.

1. You have a view. That view is somewhat complex but it is a dynamically rewritten query.

2. The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.

3. You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)

My recommendations:

1. Wrap the whole result set in a plpgsql function. Wrap that function in the view. OR

2. Use a WITH clause. I am less sure about this one. I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions. Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output! Let's skip and only run the second portion!" This is a feature, not a bug (and it is what allows table partitioning to be useful).

This is just an idea.

test=# CREATE INDEX items_idx ON items (person_id);
CREATE INDEX

test=# SELECT person_id, fname, 
    (SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
 FROM people;
 person_id | fname |     titles     
-----------+-------+----------------
         1 | Bob   | Cat,Dog,Monkey
         2 | Jim   | Elephant
         3 | Geoff | 
(3 rows)

I don't know what application language you use (PHP, Ruby, or others), but all languages can easily parse this result.

I made dummy data and done EXPLAIN. The result is shown below:

test=# EXPLAIN 
SELECT person_id,
fname, 
(SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
 FROM people;
                                       QUERY PLAN                                     

--------------------------------------------------------------------------------------
---
                                        QUERY PLAN                                    

--------------------------------------------------------------------------------------
-----
 Seq Scan on people  (cost=0.00..984256.31 rows=9990 width=8)
   SubPlan 2
     ->  Result  (cost=98.50..98.51 rows=1 width=0)
           InitPlan 1 (returns $1)
             ->  Bitmap Heap Scan on items  (cost=19.81..98.50 rows=455 width=16)
                   Recheck Cond: (person_id = people.person_id)
                   ->  Bitmap Index Scan on items_idx  (cost=0.00..19.70 rows=455 widt
h=0)
                         Index Cond: (person_id = people.person_id)
(8 rows)

This query uses Bitmap Index scan, so it can be run very fast.

Please don't forget to create index of person_id in the items table.