From: Matt Clark <matt@ymogen.net>
To: rigmor.ukuhe@finestmedia.com
To: pgsql-performance@postgresql.org
Subject: Re: Index problem
Date: Thu, 25 Sep 2003 13:13:32 +0100
Message-ID: <OAEAKHEHCMLBLIDGAFELKEBIDMAA.matt@ymogen.net> (raw)
In-Reply-To: <OEEHLFAIJHHMABJPIANIMEMICFAA.rigmor.ukuhe@finestmedia.com>
> There are about 2500 rows in that table.
>
> 1st query explain analyze: Seq Scan on PRIORITY_STATISTICS
> (cost=0.00..491.44 rows=127 width=12) (actual time=98.58..98.58 rows=0
> loops=1)
> Total runtime: 98.74 msec
>
> 2nd query explain analyze: NOTICE: QUERY PLAN:
>
> Index Scan using PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
[snip]
> PRIORITY_STATISTICS_reo_id on PRIORITY_STATISTICS (cost=0.00..394.06
> rows=102 width=12) (actual time=20.93..20.93 rows=0 loops=1)
> Total runtime: 21.59 msec
With only 2500 rows the planner could be deciding that it's going to have to read every disk block to do an index scan anyway, so it
might as well do a sequential scan. If the pages are in fact in the kernel cache then the compute time will dominate, not the IO
time, so it ends up looking like a bad plan, but it's probably not really such a bad plan...
Is your effective_cache_size set to something sensibly large?
You could also try decreasing cpu_index_tuple_cost and cpu_tuple_cost. These will affect all your queries though, so what you gain
on one might be lost on another.
Matt
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: matt@ymogen.net, rigmor.ukuhe@finestmedia.com
Subject: Re: Index problem
In-Reply-To: <OAEAKHEHCMLBLIDGAFELKEBIDMAA.matt@ymogen.net>
* 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