Обсуждение: Interesting performance behaviour

#postgresql on Freenode recommended I post this here.

I'm seeing some odd behaviour with LIMIT. The query plans are included
here, as are the applicable table and index definitions. All table,
index, and query information can be found in a standard dbmail 1.2.6
install, if anyone wants to try setting up an exactly similar system.

Version: PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC
i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-3)
OS: Debian Linux, "unstable" tree

Some settings that I was told to include (as far as I am aware, these
are debian default values):
shared_buffers = 1000
sort_mem = 1024
effective_cache_size = 1000


Table/index definitions:

                                       Table "public.messages"
    Column     |              Type              |                     Modifiers
---------------+--------------------------------+----------------------------------------------------
 message_idnr  | bigint                         | not null default
nextval('message_idnr_seq'::text)
 mailbox_idnr  | bigint                         | not null default 0
 messagesize   | bigint                         | not null default 0
 seen_flag     | smallint                       | not null default 0
 answered_flag | smallint                       | not null default 0
 deleted_flag  | smallint                       | not null default 0
 flagged_flag  | smallint                       | not null default 0
 recent_flag   | smallint                       | not null default 0
 draft_flag    | smallint                       | not null default 0
 unique_id     | character varying(70)          | not null
 internal_date | timestamp(6) without time zone |
 status        | smallint                       | not null default 0
 rfcsize       | bigint                         | not null default 0
 queue_id      | character varying(40)          | not null default
''::character varying
Indexes:
    "messages_pkey" primary key, btree (message_idnr)
    "idx_mailbox_idnr_queue_id" btree (mailbox_idnr, queue_id)
Foreign-key constraints:
    "ref141" FOREIGN KEY (mailbox_idnr) REFERENCES
mailboxes(mailbox_idnr) ON UPDATE CASCADE ON DELETE CASCADE




EXPLAIN ANALYZE results:


 EXPLAIN ANALYZE SELECT message_idnr FROM messages WHERE mailbox_idnr
= 1746::bigint AND status<2::smallint AND seen_flag = 0 AND unique_id
!= '' ORDER BY message_idnr ASC LIMIT 1;
                                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..848.36 rows=1 width=8) (actual
time=1173.949..1173.953 rows=1 loops=1)
   ->  Index Scan using messages_pkey on messages
(cost=0.00..367338.15 rows=433 width=8) (actual
time=1173.939..1173.939 rows=1 loops=1)
         Filter: ((mailbox_idnr = 1746::bigint) AND (status <
2::smallint) AND (seen_flag = 0) AND ((unique_id)::text <> ''::text))
 Total runtime: 1174.012 ms


EXPLAIN ANALYZE SELECT message_idnr FROM messages WHERE mailbox_idnr =
1746::bigint AND status<2::smallint AND seen_flag = 0 AND unique_id !=
'' ORDER BY message_idnr ASC ;
                                                                   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2975.42..2976.50 rows=433 width=8) (actual
time=2.357..2.545 rows=56 loops=1)
   Sort Key: message_idnr
   ->  Index Scan using idx_mailbox_idnr_queue_id on messages
(cost=0.00..2956.46 rows=433 width=8) (actual time=0.212..2.124
rows=56 loops=1)
         Index Cond: (mailbox_idnr = 1746::bigint)
         Filter: ((status < 2::smallint) AND (seen_flag = 0) AND
((unique_id)::text <> ''::text))
 Total runtime: 2.798 ms


I see a similar speedup (and change in query plan) using "LIMIT 1
OFFSET <anything besides 0>".

Joey,

> shared_buffers = 1000
> sort_mem = 1024
> effective_cache_size = 1000

effective_cache_size should be much higher, like 3/4 of your available RAM.
This is probably the essence of your planner problem; the planner thinks you
have no RAM.

> I see a similar speedup (and change in query plan) using "LIMIT 1
> OFFSET <anything besides 0>".

So what's your problem?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

> > shared_buffers = 1000
> > sort_mem = 1024
> > effective_cache_size = 1000
>
> effective_cache_size should be much higher, like 3/4 of your available RAM.
> This is probably the essence of your planner problem; the planner thinks you
> have no RAM.

I set effective_cache_size to 64000 on a machine with 2GB of physical
RAM, and the behaviour is exactly the same.

Accidentally sent directly to Josh.


---------- Forwarded message ----------
From: Joey Smith <joeysmith@gmail.com>
Date: Fri, 10 Sep 2004 15:57:49 -0600
Subject: Re: [PERFORM] Interesting performance behaviour
To: josh@agliodbs.com

> > I see a similar speedup (and change in query plan) using "LIMIT 1
> > OFFSET <anything besides 0>".
>
> So what's your problem?

The problem is that "LIMIT 1 OFFSET 0" has such poor performance. I'm
not so much worried about the query time (it's still low enough to be
acceptable), but the fact that it behaves oddly raised the question of
whether this was correct behaviour or not. I'll try it with a saner
value for effective_cache_size.

Joey Smith <joeysmith@gmail.com> writes:
>  EXPLAIN ANALYZE SELECT message_idnr FROM messages WHERE mailbox_idnr
> = 1746::bigint AND status<2::smallint AND seen_flag = 0 AND unique_id
> != '' ORDER BY message_idnr ASC LIMIT 1;
>                                                                 QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.00..848.36 rows=1 width=8) (actual
> time=1173.949..1173.953 rows=1 loops=1)
>    ->  Index Scan using messages_pkey on messages
> (cost=0.00..367338.15 rows=433 width=8) (actual
> time=1173.939..1173.939 rows=1 loops=1)
>          Filter: ((mailbox_idnr = 1746::bigint) AND (status <
> 2::smallint) AND (seen_flag = 0) AND ((unique_id)::text <> ''::text))
>  Total runtime: 1174.012 ms

The planner is correctly estimating that this plan is very expensive
overall --- but it is guessing that the indexscan will only need to be
run 1/433'd of the way to completion before the single required row is
found.  So that makes it look like a slightly better bet than the more
conventional indexscan-on-mailbox_idnr-and-then-sort plan.  If you ask
for a few more than one row, though, it stops looking like a good bet,
since each additional row is estimated to cost another 1/433'd of the
total cost.

Part of the estimation error is that there are only 56 matching rows
not 433, so the real cost-per-row ought to be 1/56'th of the total
indexscan cost.  I suspect also that there is some correlation between
message_idnr and mailbox_idnr, which results in having to scan much
more than the expected 1/56'th of the index before finding a matching
row.

The planner has no stats about intercolumn correlation so it's not going
to be able to recognize the correlation risk, but if you could get the
rowcount estimate closer to reality that would be enough to tilt the
scales to the better plan.  Increasing ANALYZE's stats target for
mailbox_idnr would be worth trying.  Also, I suspect that there is a
strong correlation between seen_flag and status, no?  This again is
something you can't expect the planner to realize directly, but you
might be able to finesse the problem (and save some storage as well)
if you could merge the seen_flag into the status column and do just one
comparison to cover both conditions.

            regards, tom lane