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 09:22:50 -0600
Message-ID: <dcc563d10904060822v3d9ce49fy359c9f9753034f19@mail.gmail.com> (raw)
In-Reply-To: <49DA16C9.8050405@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>
<dcc563d10904060747l52522a62x17d061166e1576f2@mail.gmail.com>
<49DA16C9.8050405@megafon.hr>
On Mon, Apr 6, 2009 at 8:50 AM, Mario Splivalo
<mario.splivalo@megafon.hr> wrote:
> Scott Marlowe wrote:
>>>
>>> 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.
>
> I'm using 8.3.7. So, you'd also suggest to keep that extra (in a way
> redundant) index on field_name, since I need PK on (photo_id, field_name) ?
Either that or reverse the terms in the pk.
Also, you might want to look at adjusting random_page_access to
something around 1.5 to 2.0.
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: <dcc563d10904060822v3d9ce49fy359c9f9753034f19@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> <49DA16C9.8050405@megafon.hr> <dcc563d10904060822v3d9ce49fy359c9f9753034f19@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