Hi, postgres does a seqscan, even though there is an index present and it should be much more efficient to use it. I tried to synthetically reproduce it, but it won't make the same choice when i do. I can reproduce it with a simplified set of the data itself though. here's the query, and the analyzed plan: select count(*) from d2 join g2 on g2.gid=d2.gid where g2.k=1942 Aggregate (cost=60836.71..60836.72 rows=1 width=0) (actual time=481.526..481.526 rows=1 loops=1) -> Hash Join (cost=1296.42..60833.75 rows=1184 width=0) (actual time=317.403..481.513 rows=17 loops=1) Hash Cond: (d2.gid = g2.gid) -> Seq Scan on d2 (cost=0.00..47872.54 rows=3107454 width=8) (actual time=0.013..231.707 rows=3107454 loops=1) -> Hash (cost=1290.24..1290.24 rows=494 width=8) (actual time=0.207..0.207 rows=121 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 5kB -> Index Scan using g_blok on g2 (cost=0.00..1290.24 rows=494 width=8) (actual time=0.102..0.156 rows=121 loops=1) Index Cond: (k = 1942) Total runtime: 481.600 ms Here's the DDL: create table g2 (gid bigint primary key, k integer); create table d2 (id bigint primary key, gid bigint); --insert into g2 (...) --insert into d2 (...) create index g_blok on g2(blok); create index d_gid on d2(gid); alter table d2 add constraint d_g_fk foreign key (gid) references g2 (gid); analyze d2; analyze g2; Any advice? Cheers, Willy-Bas Loos -- So, did you try to set: enable_seqscan = off and see if different execution plan is more efficient? Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
