Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGzc7-0007x3-GM for pgsql-performance@postgresql.org; Wed, 26 Sep 2012 21:57:51 +0000 Received: from mail-bk0-f46.google.com ([209.85.214.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGzc5-0002fm-Ee for pgsql-performance@postgresql.org; Wed, 26 Sep 2012 21:57:50 +0000 Received: by bkcjk13 with SMTP id jk13so726138bkc.19 for ; Wed, 26 Sep 2012 14:57:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:content-type:subject:message-id:date:to:mime-version:x-mailer; bh=eRNZgWYqWIiRaEthdzcfzMAg8nj8MFU+Nx6Fhw8Ojb4=; b=I9rPpitwZT4LMp++aCK06JSjozythZ9KhIBgj5bLehXSz5JUegw24ngwRcnPYgC8mh qm8b5Qi9e2n1jLU2gdNSa8+ioqMcQPi2Dr126Ne3UwHd6mYeCiKBgDpgTDUdAOsyIEEy BUPyS36Hn1pTDN9OxBT0CkX9YXD15nt0IDYeSUdXk58GssA7Y3TmO9zpGOM5JzwxLDVc eGpBJGU2CydnCdKHsDMGEtopofCqOHE+YOGNbUaf79pwF5QGDI9kjirFSuduBogUZjAy 9JpG+PDeRp63a01OMC8u/G0V5GyvfwfXhAY5ayVCUPYZ2mjWAKTyD6imjLZykOInWdwV giPw== Received: by 10.112.26.197 with SMTP id n5mr940827lbg.18.1348696668142; Wed, 26 Sep 2012 14:57:48 -0700 (PDT) Received: from [192.168.20.5] (37-144-24-89.broadband.corbina.ru. [37.144.24.89]) by mx.google.com with ESMTPS id hu6sm967851lab.13.2012.09.26.14.57.46 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 26 Sep 2012 14:57:47 -0700 (PDT) From: Evgeny Shishkin Content-Type: multipart/alternative; boundary="Apple-Mail=_73183963-7690-4B85-B70F-23542F7DB38E" Subject: wrong join result set estimate Message-Id: <5C1F9E10-588B-478C-ADC7-84B2CBA504F4@gmail.com> Date: Thu, 27 Sep 2012 01:57:46 +0400 To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 6.1 \(1498\)) X-Mailer: Apple Mail (2.1498) X-Pg-Spam-Score: -1.3 (-) X-Archive-Number: 201209/211 X-Sequence-Number: 47957 --Apple-Mail=_73183963-7690-4B85-B70F-23542F7DB38E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hello, i have a problem with relatively easy query. EXPLAIN ANALYZE SELECT content.* FROM content JOIN blog ON blog.id =3D = content.blog_id JOIN community_prop ON blog.id =3D = community_prop.blog_id JOIN community ON community.id =3D = community_prop.id WHERE community.id IN (33, 55, 61, 1741, 75, 90, 106, = 180, 228, 232, 256, 310, 388, 404, 504, 534, 536, 666, 700, 768, 824, = 832, 855, 873, 898, 962, 1003, 1008, 1027, 1051, 1201, 1258, 1269, 1339, = 1355, 1360, 1383, 1390, 1430, 1505, 1506, 1530, 1566, 1578, 1616, 1678, = 1701, 1713, 1723, 1821, 1842, 1880, 1882, 1894, 1973, 2039, 2069, 2106, = 2130, 2204, 2226, 2236, 2238, 2263, 2272, 2310, 2317, 2327, 2353, 2360, = 2401, 2402, 2409, 2419, 2425, 2426, 2438, 2440, 2452, 2467, 2494, 2514, = 2559, 2581, 2653, 2677, 2679, 2683, 2686, 2694, 2729, 2732, 2739, 2779, = 2785, 2795, 2821, 2831, 2839, 2862, 2864, 2866, 2882, 2890, 2905, 2947, = 2962, 2964, 2978, 2981, 3006, 3016, 3037, 3039, 3055, 3060, 3076, 3112, = 3124, 3135, 3138, 3186, 3213, 3222, 3225, 3269, 3273, 3288, 3291, 3329, = 3363, 3375, 3376, 3397, 3415, 3491, 3500, 2296, 3547, 129, 1039, 8, = 1053, 1441, 2372, 1974, 289, 2449, 2747, 2075, 57, 3550, 3069, 89, 1603, = 1570, 54, 152, 1035, 1456, 506, 1387, 43, 1805, 1851, 1843, 2587, 1908, = 1790, 2630, 901, 13, 529, 705, 81, 2668, 1086, 603, 1986, 2516, 2969, = 2671, 568, 4636, 1115, 864, 381, 4516, 2608, 677, 88, 1825, 3220, 3284, = 947, 1190, 2233, 4489, 3320, 2957, 4146, 1841, 25, 643, 4352, 14, 4261, = 3876, 1311, 1342, 4057, 3974) ORDER BY content.time_create DESC LIMIT = 10; http://explain.depesz.com/s/ccE As you can see, planner estimates 115 rows, but there are 259554 of = them. This query shows root of the problem EXPLAIN ANALYZE SELECT content.* FROM content JOIN blog ON blog.id =3D = content.blog_id JOIN community_prop ON blog.id =3D = community_prop.blog_id; QUERY PLAN = =20 = --------------------------------------------------------------------------= ----------------------------------------------------------- Hash Join (cost=3D24498.17..137922.26 rows=3D2624 width=3D572) (actual = time=3D36.028..1342.267 rows=3D408374 loops=3D1) Hash Cond: (content.blog_id =3D blog.id) -> Seq Scan on content (cost=3D0.00..102364.99 rows=3D1260899 = width=3D572) (actual time=3D0.030..983.274 rows=3D1256128 loops=3D1) -> Hash (cost=3D24439.07..24439.07 rows=3D4728 width=3D8) (actual = time=3D35.964..35.964 rows=3D4728 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 185kB -> Nested Loop (cost=3D0.00..24439.07 rows=3D4728 width=3D8) = (actual time=3D0.064..33.092 rows=3D4728 loops=3D1) -> Seq Scan on community_prop (cost=3D0.00..463.28 = rows=3D4728 width=3D4) (actual time=3D0.004..5.089 rows=3D4728 loops=3D1) -> Index Scan using blog_pkey on blog (cost=3D0.00..5.06 = rows=3D1 width=3D4) (actual time=3D0.005..0.005 rows=3D1 loops=3D4728) Index Cond: (id =3D community_prop.blog_id) Total runtime: 1361.354 ms 2624 vs 408374 Joining only content with blog: 1260211 vs 1256124. Joining only blog with community_prop: 4728 vs 4728 Joining only content with community_prop: 78304 vs 408376 SHOW default_statistics_target ; default_statistics_target=20 --------------------------- 500 I already altered stats on blog_id column=20 ALTER TABLE content ALTER COLUMN blog_id SET STATISTICS 1000; Tried setting 3000 and 10000 on all join columns - did not make a = difference. Tried setting n_distinct on content(blog_id) manually to different = values from 10000 to 200000 (exact distinct is 90k, vacuum sets it to = 76k) - did not change the estimate result set, only estimated index = lookup. Don't now what to do with this. Ready to provide any additional information. Thank you for your time.= --Apple-Mail=_73183963-7690-4B85-B70F-23542F7DB38E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii http://explain.depesz.com/s/ccE

As you can see, planner estimates 115 rows, = but there are 259554 of them.

This query = shows root of the problem
EXPLAIN ANALYZE SELECT = content.* FROM content JOIN blog ON blog.id =3D content.blog_id JOIN = community_prop ON blog.id =3D community_prop.blog_id;
  =                     =                     =                   =  QUERY PLAN                 =                     =                     =     =  
---------------------------------------------------------= --------------------------------------------------------------------------= --
 Hash Join  (cost=3D24498.17..137922.26 rows=3D2624= width=3D572) (actual time=3D36.028..1342.267 rows=3D408374 = loops=3D1)
   Hash Cond: (content.blog_id =3D = blog.id)
   ->  Seq Scan on content =  (cost=3D0.00..102364.99 rows=3D1260899 width=3D572) (actual = time=3D0.030..983.274 rows=3D1256128 loops=3D1)
  =  ->  Hash  (cost=3D24439.07..24439.07 rows=3D4728 = width=3D8) (actual time=3D35.964..35.964 rows=3D4728 = loops=3D1)
         Buckets: 1024 =  Batches: 1  Memory Usage: 185kB
    =      ->  Nested Loop  (cost=3D0.00..24439.07 = rows=3D4728 width=3D8) (actual time=3D0.064..33.092 rows=3D4728 = loops=3D1)
              =  ->  Seq Scan on community_prop  (cost=3D0.00..463.28 = rows=3D4728 width=3D4) (actual time=3D0.004..5.089 rows=3D4728 = loops=3D1)
              =  ->  Index Scan using blog_pkey on blog =  (cost=3D0.00..5.06 rows=3D1 width=3D4) (actual time=3D0.005..0.005 = rows=3D1 loops=3D4728)
          =            Index Cond: (id =3D = community_prop.blog_id)
 Total runtime: 1361.354 = ms

2624 = vs 408374

Joining only content with = blog: 1260211 vs 1256124.
Joining only blog with = community_prop: 4728 vs 4728
Joining only content = with community_prop: 78304 = vs 408376

SHOW = default_statistics_target = ;
 default_statistics_target 
--------------= -------------
 500

I = already altered stats on blog_id column 
ALTER TABLE = content ALTER COLUMN blog_id SET STATISTICS = 1000;

Tried setting 3000 and 10000 on all join = columns - did not make a difference.
Tried setting n_distinct = on content(blog_id) manually to different values from 10000 to 200000 = (exact distinct is 90k, vacuum sets it to 76k) - did not change the = estimate result set, only estimated index = lookup.

Don't now what to do with = this.

Ready to provide any additional = information.
Thank you for your time.
= --Apple-Mail=_73183963-7690-4B85-B70F-23542F7DB38E--