Stephan Szabo wrote:
On Wed, 22 Jan 2003, Tomasz Myrta wrote:
Tomasz Myrta writes:
I'd like to split queries into views, but I can't join them - planner
search all of records instead of using index. It works very slow.
I think this is the same issue that Stephan identified in his response
to your other posting ("sub-select with aggregate"). When you write
FROM x join y using (col) WHERE x.col = const
the WHERE-restriction is only applied to x. I'm afraid you'll need
to write
FROM x join y using (col) WHERE x.col = const AND y.col = const
Ideally you should be able to write just
FROM x join y using (col) WHERE col = const
but I think that will be taken the same as "x.col = const" :-(
I don't know if anything changed on 7.3.
I don't think so, but this is a general transitivity constraint AFAIK, not
one actually to do with views (ie, if you wrote out the query without a
view, you can run into the same issue). It's somewhat easier to run into
the case with views and the effect may be exasperated by views, but it's
a general condition.
For example:
create table a(a int);
create table c(a int);
sszabo=# explain select * from a join c using (a) where a=3;
QUERY PLAN
-------------------------------------------------------------
Hash Join (cost=1.01..26.08 rows=6 width=8)
Hash Cond: ("outer".a = "inner".a)
-> Seq Scan on c (cost=0.00..20.00 rows=1000 width=4)
-> Hash (cost=1.01..1.01 rows=1 width=4)
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
Filter: (a = 3)
(6 rows)
I don't understand your idea.
explain select * from przystanki p join miasta m using (id_miasta) where field_id=100
Both tables are indexed on field id_miasta. They have enough rows to use indexes.
Nested Loop (cost=0.00..9.48 rows=1 width=64)
-> Index Scan using ind_miasto_przyst on przystanki p (cost=0.00..5.54 rows=1 width=41)
-> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=23)
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
