pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Mario Splivalo <mario.splivalo@megafon.hr>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Date: Mon, 6 Apr 2009 08:47:07 -0600
Message-ID: <dcc563d10904060747l52522a62x17d061166e1576f2@mail.gmail.com> (raw)
In-Reply-To: <49DA13C1.2030200@megafon.hr>
References: <49D0D237.7020308@megafon.hr>
	<3028.1238422571@sss.pgh.pa.us>
	<49D0E687.50605@megafon.hr>
	<dcc563d10903300925m88a73c7kf90084b5e088a445@mail.gmail.com>
	<49D9F39F.40106@megafon.hr>
	<dcc563d10904060658v621cbd3drbb75b6428d770b04@mail.gmail.com>
	<49DA13C1.2030200@megafon.hr>

On Mon, Apr 6, 2009 at 8:37 AM, Mario Splivalo
<mario.splivalo@megafon.hr> wrote:
> Scott Marlowe wrote:
>>
>> On Mon, Apr 6, 2009 at 6:20 AM, Mario Splivalo
>> <mario.splivalo@megafon.hr> wrote:
>>>
>>> Scott Marlowe wrote:
>>>>
>>>> It's not really solved, it's just a happy coincidence that the current
>>>> plan runs well.  In order to keep the query planner making good
>>>> choices you need to increase stats target for the field in the index
>>>> above.  The easiest way to do so is to do this:
>>>>
>>>> alter database mydb set default_statistics_target=100;
>>>>
>>>> and run analyze again:
>>>>
>>>> analyze;
>>>
>>> So, i removed the index on field_name, set
>>> default_default_statistics_target
>>> to 100, analyzed, and the results are the same:
>>
>> Why did you remove the index?
>>
>
> Because I already have index on that column, index needed to enforce PK
> constraint. Here is the original DDL for the table:
>
> CREATE TABLE photo_info_data
> (
>  photo_id integer NOT NULL,
>  field_name character varying NOT NULL,
>  field_value character varying,
>  CONSTRAINT photo_info_data_pk PRIMARY KEY (user_id, field_name)
> )
>
> CREATE INDEX photo_info_data_ix_field_value
>  ON user_info_data USING btree (field_value);
>
> So, there is index on (user_id, field_name). Postgres is using index for
> user_id (...WHERE user_id = 12345) but not on field-name (...WHERE
> field_name = 'f-spot'). When I add extra index on field name:
>
> CREATE INDEX photo_info_data_ix__field_name
>  ON user_info_data USING btree (field_name);
>
> Then that index is used.

On older versions of pgsql, the second of two terms in a multicolumn
index can't be used alone.  On newer versions it can, but it is much
less efficient than if it's a single column index or if the term is
the first one not the second.



view thread (10+ messages)  latest in thread

Message-ID: <dcc563d10904060747l52522a62x17d061166e1576f2@mail.gmail.com>
Permalink:  ../dcc563d10904060747l52522a62x17d061166e1576f2@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10904060747l52522a62x17d061166e1576f2@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, mario.splivalo@megafon.hr, tgl@sss.pgh.pa.us
  Subject: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
  In-Reply-To: <dcc563d10904060747l52522a62x17d061166e1576f2@mail.gmail.com>
  References: <49D0D237.7020308@megafon.hr> <3028.1238422571@sss.pgh.pa.us> <49D0E687.50605@megafon.hr> <dcc563d10903300925m88a73c7kf90084b5e088a445@mail.gmail.com> <49D9F39F.40106@megafon.hr> <dcc563d10904060658v621cbd3drbb75b6428d770b04@mail.gmail.com> <49DA13C1.2030200@megafon.hr> <dcc563d10904060747l52522a62x17d061166e1576f2@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox