Re: Unexpected query plan results

Dave Dutcher wrote:

>>-----Original Message-----
>>From: Anne Rosset
>>Subject: Re: [PERFORM] Unexpected query plan results
>>
>>
>>
>>>>SELECT SUM(1) FROM item WHERE is_deleted = 'f'; sum
>>>>
>>>>
>>--------- 1824592
>>
>>
>>>>(1
>>>>row)
>>>>SELECT SUM(1) FROM item WHERE folder_id = 'tracker3641
>>>></sf/sfmain/do/go/tracker3641?returnUrlKey=1243878161701>'; sum
>>>>--------
>>>>122412 (1 row)
>>>>SELECT SUM(1) FROM item WHERE folder_id = 'tracker3641
>>>></sf/sfmain/do/go/tracker3641?returnUrlKey=1243878161701>' AND
>>>>is_deleted = 'f'; sum ----- 71 (1 row) SELECT SUM(1) FROM
>>>>
>>>>
>>item WHERE
>>
>>
>>>>folder_id = 'tracker3641
>>>></sf/sfmain/do/go/tracker3641?returnUrlKey=1243878161701>' AND
>>>>is_deleted = 't'; sum -------- 122341 (1 row)
>>>>
>>>>
>>>>
>>>>
>>>Something's not right here.  If the whole table has only
>>>
>>>
>>468173 rows,
>>
>>
>>>you can't have 1.8 million deleted rows where is_deleted = false.
>>>
>>>...Robert
>>>
>>>
>>>
>>>
>>The item table has 2324829 rows
>>The artifact table has 468173 rows.
>>Thanks,
>>Anne
>>
>>
>
>I'd been thinking about the sort, but I hadn't thought yet if that index
>scan on item could be made faster.  Could you post the table definition of
>item including the indexes on it?
>
>Dave
>
>
>
>
>
>
Dave:
                   Table "public.item"
       Column        |           Type           | Modifiers
---------------------+--------------------------+-----------
 id                  | character varying(32)    | not null
 name                | character varying(128)   |
 title               | character varying(255)   |
 version             | integer                  | not null
 date_created        | timestamp with time zone | not null
 date_last_modified  | timestamp with time zone | not null
 is_deleted          | boolean                  | not null
 type_id             | character varying(32)    |
 folder_id           | character varying(32)    |
 planning_folder_id  | character varying(32)    |
 created_by_id       | character varying(32)    |
 last_modified_by_id | character varying(32)    |
Indexes:
    "item_pk" primary key, btree (id)
    "item_created_by_id" btree (created_by_id)
    "item_date_created" btree (date_created)
    "item_folder" btree (folder_id)
    "item_name" btree (name)
    "item_planning_folder" btree (planning_folder_id)

Thanks,
Anne