Message-ID: <30c6caeda38eb66a15665d147a5db451.squirrel@sq.gransy.com>
In-Reply-To: <201106071802.08272.anthony.shipman@symstream.com>
References: <201106071802.08272.anthony.shipman@symstream.com>
Date: Tue, 7 Jun 2011 18:40:13 +0200
Subject: Re: strange query plan with LIMIT
From: tv@fuzzy.cz
To: anthony.shipman@symstream.com
Cc: pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

> Version: PostgreSQL 8.3.5 (mammoth replicator)
>
> Schema:
>
> CREATE TABLE tdiag (
>     diag_id             integer DEFAULT nextval('diag_id_seq'::text),
>     create_time		timestamp with time zone default now(),	/* time this
> record
> was created */
>     diag_time           timestamp with time zone not null,
>     device_id           integer,                /* optional */
>     fleet_id            integer,                /* optional */
>     customer_id         integer,                /* optional */
>     module              character varying,
>     node_kind           smallint,
>     diag_level          smallint,
>     tag                 character varying not null default '',
>     message             character varying not null default '',
>     options             text,
>
>     PRIMARY KEY (diag_id)
> );
>
> create index tdiag_create_time   ON tdiag(create_time);
>
> The number of rows is over 33 million with time stamps over the past two
> weeks.
>
> The create_time order is almost identical to the id order.  What I want
> to find is the first or last entry by id in a given time range. The
> query I am having a problem with is:

Hi,

why are you reposting this? Pavel Stehule already recommended you to run
ANALYZE on the tdiag table - have you done that? What was the effect?

The stats are off - e.g. the bitmap scan says

   ->  Bitmap Heap Scan on tdiag  (cost=25763.48..638085.13 rows=1141019
width=114) (actual time=43.232..322.441 rows=86530 loops=1)

so it expects to get 1141019 rows but it gets 86530, i.e. about 7% of the
expected number. That might be enough to cause bad plan choice and thus
performance issues.

And yet another recommendation - the sort is performed on disk, so give it
more work_mem and it should be much faster (should change from "merge
sort" to "quick sort"). Try something like work_mem=20MB and see if it
does the trick.

regards
Tomas