Skip site navigation (1) Skip section navigation (2)

Query planner always has exactly half of the rows in the table as "plan rows" with new GIST operator class

From: Adrian Schreyer <ams214(at)cam(dot)ac(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Query planner always has exactly half of the rows in the table as "plan rows" with new GIST operator class
Date: 2011-10-31 17:52:05
Message-ID: CACYduyLe4+ECLHp=_PZ5R8YFBZMWV4S1W=qRWdS3FvNQuuQjfQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
I have added a GIST operator class to a custom data type in
PostgreSQL. The index returns the correct results and the build speed
is fairly good as well. There is one problem however that is
presumably linked to the picksplit function (?) - the query planner
always returns half  of all the rows in the table as (after vacuum)
"Plan Rows". In a table with 1M rows, it will be exactly 500k. Since
this seems to be systematic and the gap between the plan rows and
actual rows is orders of magnitude, there must be a bug in my GIST
implementation.

On a table with 200 rows I get the following plan (slightly truncated
for visibility):

 [
   {
     "Plan": {
       "Node Type": "Bitmap Heap Scan",
       "Relation Name": "bar",
       "Schema": "public",
       "Alias": "foo",
       "Startup Cost": 25.03,
       "Total Cost": 34.53,
       "Plan Rows": 100,
       "Plan Width": 272,
       "Actual Startup Time": 0.097,
       "Actual Total Time": 0.214,
       "Actual Rows": 16,
       "Actual Loops": 1,
       "Output": ["id", "(
       "Recheck Cond": "(foo.bar %? '(
       "Plans": [
         {
           "Node Type": "Bitmap Index Scan",
           "Parent Relationship": "Outer",
           "Index Name": "idx_fps",
           "Startup Cost": 0.00,
           "Total Cost": 25.00,
           "Plan Rows": 100,
           "Plan Width": 0,
           "Actual Startup Time": 0.069,
           "Actual Total Time": 0.069,
           "Actual Rows": 16,
           "Actual Loops": 1,
           "Index Cond": "(
         }
       ]
     },
     "Triggers": [
     ],
     "Total Runtime": 0.327
   }
 ]

This is how my picksplit function splits the entries (200 rows in table):

NOTICE:  GIST split vector (n 26): 11 left, 15 right.
 NOTICE:  GIST split vector (n 26): 8 left, 18 right.
 NOTICE:  GIST split vector (n 26): 17 left, 9 right.
 NOTICE:  GIST split vector (n 26): 16 left, 10 right.
 NOTICE:  GIST split vector (n 26): 20 left, 6 right.
 NOTICE:  GIST split vector (n 26): 14 left, 12 right.
 NOTICE:  GIST split vector (n 26): 19 left, 7 right.
 NOTICE:  GIST split vector (n 26): 23 left, 3 right.
 NOTICE:  GIST split vector (n 26): 24 left, 2 right.

INFO:  index "idx_fps" now contains 200 row versions in 11 pages

Any ideas what the cause might be?

Responses

pgsql-general by date

Next:From: Tarlika Elisabeth SchmitzDate: 2011-10-31 18:18:08
Subject: Re: [PL/pgSQL] function call
Previous:From: Pavel StehuleDate: 2011-10-31 15:01:53
Subject: Re: [PL/pgSQL] function call

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group