Re: Slow join on partitioned table

On Fri, Mar 4, 2011 at 6:40 AM, Mark Thornton <mthornton(at)optrak(dot)co(dot)uk> wrote:
> The query plan appends sequential scans on the tables in the partition (9
> tables, ~4 million rows) and then hash joins that with a 14 row table. The
> join condition is the primary key of each table in the partition (and would
> be the primary key of the parent if that was supported).
> It would be much faster if it did an index scan on each of the child tables
> and merged the results.
>
> I can achieve this manually by rewriting the query as a union between
> queries against each of the child tables. Is there a better way? (I'm using
> PostGreSQL 8.4 with PostGIS 1.4).

Can you post the EXPLAIN ANALYZE output of the other formulation of the query?

>               ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140 width=8)
> (actual time=0.006..0.012 rows=14 loops=1)

That seems quite surprising. There are only 14 rows in the table but
PG thinks 2140? Do you have autovacuum turned on? Has this table
been analyzed recently?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company