X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 028D85E46D1 for ; Wed, 25 Aug 2004 05:19:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49444-05 for ; Wed, 25 Aug 2004 08:19:50 +0000 (GMT) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [202.0.58.21]) by svr1.postgresql.org (Postfix) with ESMTP id EC9FD5E46CE for ; Wed, 25 Aug 2004 05:19:48 -0300 (ADT) Received: from smtp-1.paradise.net.nz (smtp-1b.paradise.net.nz [202.0.32.210]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0I2Z004AYTSXUK@linda-2.paradise.net.nz> for pgsql-performance@postgresql.org; Wed, 25 Aug 2004 20:19:46 +1200 (NZST) Received: from [192.168.1.11] (218-101-13-189.paradise.net.nz [218.101.13.189]) by smtp-1.paradise.net.nz (Postfix) with ESMTP id 93F6282850; Wed, 25 Aug 2004 20:19:45 +1200 (NZST) Date: Wed, 25 Aug 2004 20:22:18 +1200 From: Mark Kirkwood Subject: Re: What is the best way to do attribute/values? In-reply-to: <200408241330.32484.josh@agliodbs.com> To: Josh Berkus Cc: Daniel Ceregatti , pgsql-performance@postgresql.org Message-id: <412C4C3A.2030001@coretech.co.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.8a2) Gecko/20040715 References: <4124EB58.9040102@sh.nu> <200408241330.32484.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/333 X-Sequence-Number: 7969 Josh Berkus wrote: > Things we've already tried to avoid going over old ground: > >1) increasing statistics; >2) increasing sort_mem (to 256MB, which is overkill) >3) testing on 8.0 beta, which does not affect the issue. > >At this point I'm looking for ideas. Suggestions, anyone? > > > with respect to query design: consider instead of: select pav1.person_id from person_attributes_vertical pav1, person_attributes_vertical pav2 where pav1.attribute_id = 1 and pav1.value_id in (2,3) and pav2.attribute_id = 2 and pav2.value_id in (2,3) and pav1.person_id = pav2.person_id try: select pav1.person_id from person_attributes_vertical pav1 where ( pav1.attribute_id = 1 and pav1.value_id in (2,3)) or ( pav1.attribute_id = 2 and pav1.value_id in (2,3)) I am gambling that the 'or's' might be less expensive than the multiple self joins (particularly in the more general cases!). To make access work well you might want to have *several* concatenated indexes of 2 -> 4 attributes - to work around Pg inability to use more than 1 in a given query. For this query indexing (attribute_id, value_id) is probably good. Consider playing with 'random_page_cost' and maybe 'effective_cache_size' to encourage the planner to use 'em. regards Mark