From: tv@fuzzy.cz
To: anthony.shipman@symstream.com
Cc: pgsql-performance@postgresql.org
Subject: Re: strange query plan with LIMIT
Date: Tue, 7 Jun 2011 18:40:13 +0200
Message-ID: <30c6caeda38eb66a15665d147a5db451.squirrel@sq.gransy.com> (raw)
In-Reply-To: <201106071802.08272.anthony.shipman@symstream.com>
References: <201106071802.08272.anthony.shipman@symstream.com>
> 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
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: tv@fuzzy.cz, anthony.shipman@symstream.com
Subject: Re: strange query plan with LIMIT
In-Reply-To: <30c6caeda38eb66a15665d147a5db451.squirrel@sq.gransy.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