X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 6D4899DC9D6 for ; Wed, 11 Jan 2006 06:59:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47539-03 for ; Wed, 11 Jan 2006 06:59:55 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.203]) by postgresql.org (Postfix) with ESMTP id 649C79DC9CE for ; Wed, 11 Jan 2006 06:59:50 -0400 (AST) Received: by zproxy.gmail.com with SMTP id z31so121753nzd for ; Wed, 11 Jan 2006 02:59:53 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:mime-version:content-transfer-encoding:message-id:content-type:to:from:subject:date:x-mailer; b=eDZ/UrOeWupUtg4x8ymAwSfINYlrv8T8URkgsn+NHvOpd91vzgbnoWYNKunronbsbOHkHI17zh1+vsHudnp3F1DnvJ4ql/yrbjcChYKt6sbcA12eqJVKGXzQoh9bJMhzZNvN5IyQg/EHlVObrCVoXOAnjO60D/z97TI857AaItI= Received: by 10.65.239.15 with SMTP id q15mr169429qbr; Wed, 11 Jan 2006 02:59:53 -0800 (PST) Received: from ?10.80.1.43? ( [194.248.208.94]) by mx.gmail.com with ESMTP id q13sm115173qbq.2006.01.11.02.59.52; Wed, 11 Jan 2006 02:59:52 -0800 (PST) Mime-Version: 1.0 (Apple Message framework v746.2) Content-Transfer-Encoding: 7bit Message-Id: Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed To: pgsql-performance@postgresql.org From: Bendik Rognlien Johansen Subject: Slow query with joins Date: Wed, 11 Jan 2006 11:59:39 +0100 X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/108 X-Sequence-Number: 16586 Hello! Has anyone got any tips for speeding up this query? It currently takes hours to start. PostgreSQL v8.x on (SuSe Linux) Thanks! no_people=# explain SELECT r.id AS r_id, r.firstname || ' ' || r.lastname AS r_name, ad.id AS ad_id, ad.type AS ad_type, ad.address AS ad_address, ad.postalcode AS ad_postalcode, ad.postalsite AS ad_postalsite, ad.priority AS ad_priority, ad.position[0] AS ad_lat, ad.position[1] AS ad_lon, ad.uncertainty AS ad_uncertainty, ad.extra AS ad_extra, co.id AS co_id, co.type AS co_type, co.value AS co_value, co.description AS co_description, co.priority AS co_priority, co.visible AS co_visible, co.searchable AS co_searchable FROM people r LEFT OUTER JOIN addresses ad ON(r.id = ad.record) LEFT OUTER JOIN contacts co ON(r.id = co.record) WHERE r.deleted = false AND r.original IS NULL AND co.deleted = false AND NOT ad.deleted ORDER BY r.id; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------- Sort (cost=1152540.74..1152988.20 rows=178983 width=585) Sort Key: r.id -> Hash Join (cost=313757.11..1005334.96 rows=178983 width=585) Hash Cond: ("outer".record = "inner".id) -> Seq Scan on addresses ad (cost=0.00..428541.29 rows=4952580 width=136) Filter: (NOT deleted) -> Hash (cost=312039.95..312039.95 rows=27664 width=457) -> Hash Join (cost=94815.24..312039.95 rows=27664 width=457) Hash Cond: ("outer".record = "inner".id) -> Seq Scan on contacts co (cost=0.00..147791.54 rows=5532523 width=430) Filter: (deleted = false) -> Hash (cost=94755.85..94755.85 rows=23755 width=27) -> Index Scan using people_original_is_null on people r (cost=0.00..94755.85 rows=23755 width=27) Filter: ((deleted = false) AND (original IS NULL)) (14 rows) no_people=# \d contacts Table "public.contacts" Column | Type | Modifiers -------------+------------------------ +---------------------------------------------------------- id | integer | not null default nextval ('public.contacts_id_seq'::text) record | integer | type | integer | value | character varying(128) | description | character varying(255) | priority | integer | itescotype | integer | original | integer | source | integer | reference | character varying(32) | deleted | boolean | not null default false quality | integer | visible | boolean | not null default true searchable | boolean | not null default true Indexes: "contacts_pkey" PRIMARY KEY, btree (id) "contacts_deleted_idx" btree (deleted) "contacts_record_idx" btree (record) CLUSTER "contacts_source_reference_idx" btree (source, reference) no_people=# \d addresses Table "public.addresses" Column | Type | Modifiers -------------+------------------------ +----------------------------------------------------------- id | integer | not null default nextval ('public.addresses_id_seq'::text) record | integer | address | character varying(128) | extra | character varying(32) | postalcode | character varying(16) | postalsite | character varying(64) | description | character varying(255) | position | point | uncertainty | integer | default 99999999 priority | integer | type | integer | place | character varying(64) | floor | integer | side | character varying(8) | housename | character varying(64) | original | integer | source | integer | reference | character varying(32) | deleted | boolean | not null default false quality | integer | visible | boolean | not null default true searchable | boolean | not null default true Indexes: "addresses_pkey" PRIMARY KEY, btree (id) "addresses_deleted_idx" btree (deleted) "addresses_record_idx" btree (record) CLUSTER "addresses_source_reference_idx" btree (source, reference) no_people=# \d people Table "public.people" Column | Type | Modifiers ------------+-------------------------- +-------------------------------------------------------- id | integer | not null default nextval ('public.people_id_seq'::text) origid | integer | firstname | character varying(128) | default ''::character varying middlename | character varying(128) | default ''::character varying lastname | character varying(128) | default ''::character varying updated | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone updater | integer | relevance | real | not null default 0 phonetic | text | indexed | boolean | default false record | text | original | integer | active | boolean | default true title | character varying(128) | deleted | boolean | not null default false Indexes: "people_pkey" PRIMARY KEY, btree (id) "people_indexed_idx" btree (indexed) "people_lower_lastname_firstname_idx" btree (lower (lastname::text), lower(firstname::text)) "people_original_is_null" btree (original) WHERE original IS NULL "people_relevance_idx" btree (relevance) "person_updated_idx" btree (updated) no_people=#