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, 30 Mar 2009 10:25:36 -0600
Message-ID: <dcc563d10903300925m88a73c7kf90084b5e088a445@mail.gmail.com> (raw)
In-Reply-To: <49D0E687.50605@megafon.hr>
References: <49D0D237.7020308@megafon.hr>
<3028.1238422571@sss.pgh.pa.us>
<49D0E687.50605@megafon.hr>
On Mon, Mar 30, 2009 at 9:34 AM, Mario Splivalo
<mario.splivalo@megafon.hr> wrote:
> -> Bitmap Heap Scan on photo_info_data u (cost=2193.50..26798.74
> rows=109024 width=9) (actual time=0.025..0.030 rows=3 loops=2)
> Recheck Cond: ((u.field_name)::text = (t.key)::text)
> -> Bitmap Index Scan on photo_info_data_ix__field_name
> (cost=0.00..2166.24 rows=109024 width=0) (actual time=0.019..0.019 rows=3
> loops=2)
> So, I guess I solved my problem! :) The explain analyze still shows that row
> estimate is 'quite off' (109024 estimated vs only 3 actuall), but the query
> is light-speeded :)
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;
> I tought that having primary key (and auto-index because of primary key) on
> (photo_id, field_name) should be enough. Now I have two indexes on
> field_name, but that seems to do good.
Nope, it's about the stats collected that let the planner make the right choice.
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 using the --to and --cc options:
reply via email
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: <dcc563d10903300925m88a73c7kf90084b5e088a445@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