/m: 
Received: from unknown (HELO ?10.2.2.158?) (kouber@securax.org@10.2.2.158)
	by cocaine with ESMTPA; 24 Mar 2009 10:52:22 -0000
Message-ID: <49C8BB68.9050004@saparev.com>
Date: Tue, 24 Mar 2009 12:52:24 +0200
From: Kouber Saparev <kouber@saparev.com>
Reply-To: kouber@saparev.com
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: LIMIT confuses the planner
References: <49A295F0.5010405@saparev.com> <49A2A450.20002@archonet.com>
	<49A2DFFA.8000001@saparev.com> <18725.1235412110@sss.pgh.pa.us>
	<49A429A3.9010803@saparev.com> <17814.1237757356@sss.pgh.pa.us>
In-Reply-To: <17814.1237757356@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/343
X-Sequence-Number: 33265

Now I am experiencing similar issue with another table, called 
"message", for which there's a conditional index:

CREATE TABLE message (
   message_sid SERIAL PRIMARY KEY,
   from_profile_sid INT NOT NULL REFERENCES profile,
   to_profile_sid INT NOT NULL REFERENCES profile,
   sender_has_deleted BOOLEAN NOT NULL DEFAULT FALSE,
   receiver_has_deleted BOOLEAN NOT NULL DEFAULT FALSE,
   datetime TIMESTAMP NOT NULL DEFAULT NOW(),
   body TEXT
);

CREATE INDEX message_from_profile_idx (from_profile_sid) WHERE NOT 
sender_has_deleted;


So, again... adding a LIMIT makes the planner choose the "wrong" index.


db=# EXPLAIN ANALYZE SELECT 
          message_sid
FROM
   message
WHERE
   from_profile_sid = 1296 AND NOT sender_has_deleted
ORDER BY
   message_sid DESC;
                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=2307.70..2310.74 rows=1215 width=4) (actual 
time=0.040..0.040 rows=2 loops=1)
    Sort Key: message_sid
    Sort Method:  quicksort  Memory: 25kB
    ->  Bitmap Heap Scan on message  (cost=23.59..2245.45 rows=1215 
width=4) (actual time=0.029..0.033 rows=2 loops=1)
          Recheck Cond: ((from_profile_sid = 1296) AND (NOT 
sender_has_deleted))
          ->  Bitmap Index Scan on message_from_profile_idx 
(cost=0.00..23.28 rows=1215 width=0) (actual time=0.022..0.022 rows=2 
loops=1)
                Index Cond: (from_profile_sid = 1296)
  Total runtime: 0.068 ms
(8 rows)


db=# EXPLAIN ANALYZE SELECT
   message_sid
FROM
   message
WHERE
   from_profile_sid = 1296 AND NOT sender_has_deleted
ORDER BY
   message_sid DESC LIMIT 20;
                                                                   QUERY 
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..1461.12 rows=20 width=4) (actual 
time=0.817..932.398 rows=2 loops=1)
    ->  Index Scan Backward using message_pkey on message 
(cost=0.00..88762.80 rows=1215 width=4) (actual time=0.816..932.395 
rows=2 loops=1)
          Filter: ((NOT sender_has_deleted) AND (from_profile_sid = 1296))
  Total runtime: 932.432 ms
(4 rows)


I had already increased STATISTICS to 1000 for both from_profile_sid and 
sender_has_deleted, and vacuum analyzed respectively (also did reindex), 
but still statistical data is confusing me:


db=# SELECT n_distinct FROM pg_stats WHERE tablename='message' AND 
attname='from_profile_sid';

  n_distinct
------------
        4068
(1 row)

db=# select avg(length) from (select from_profile_sid, count(*) as 
length from message group by from_profile_sid) as freq;

  avg
----------------------
  206.5117822008693663
(1 row)


Any ideas/thoughts?


-- 
Kouber Saparev
http://kouber.saparev.com