Planner is getting wrong row count

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Why is the following query getting wrong estimation of rows?
I am using Postgresql 9.2.1 with default_statistics_target = 100.
I execute vacuum analyze each night.

explain analyze
SELECT
entity.id AS "Leads_id", entity.type AS "Leads_type" ,
leads.firstname AS "Leads_firstname",
leads.lastname AS "Leads_lastname" 
FROM leads
INNER JOIN entity ON leads.leadid=entity.id
LEFT JOIN groups ON groups.groupid = entity.smownerid
LEFT join users ON entity.smownerid= users.id 
WHERE entity.type='Leads' AND entity.deleted=0  AND leads.converted=0
Hash Join  (cost=14067.90..28066.53 rows=90379 width=26) (actual time=536.009..1772.910 rows=337139 loops=1)
   Hash Cond: (leads.leadid = entity.id)
   ->  Seq Scan on leads  (cost=0.00..7764.83 rows=533002 width=18) (actual time=0.008..429.576 rows=532960 loops=1)
         Filter: (converted = 0)
   ->  Hash  (cost=9406.25..9406.25 rows=372932 width=16) (actual time=535.800..535.800 rows=342369 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 16049kB
         ->  Index Scan using entity_type_idx on entity  (cost=0.00..9406.25 rows=372932 width=16) (actual time=0.030..305.250 rows=342369 loops=1)
               Index Cond: ((type)::text = 'Leads'::text)


\d leads
                         Table "public.leads"
      Column      |          Type          |               Modifiers               
------------------+------------------------+---------------------------------------
 leadid           | integer                | not null
 email            | character varying(100) | 
 interest         | character varying(50)  | 
 firstname        | character varying(100) | 
 salutation       | character varying(200) | 
 lastname         | character varying(100) | not null
 company          | character varying(200) | not null
 annualrevenue    | integer                | default 0
 industry         | character varying(200) | 
 campaign         | character varying(30)  | 
 rating           | character varying(200) | 
 leadstatus       | character varying(50)  | 
 leadsource       | character varying(200) | 
 converted        | integer                | default 0
 designation      | character varying(200) | default 'SalesMan'::character varying
 licencekeystatus | character varying(50)  | 
 space            | character varying(250) | 
 comments         | text                   | 
 priority         | character varying(50)  | 
 demorequest      | character varying(50)  | 
 partnercontact   | character varying(50)  | 
 productversion   | character varying(20)  | 
 product          | character varying(50)  | 
 maildate         | date                   | 
 nextstepdate     | date                   | 
 fundingsituation | character varying(50)  | 
 purpose          | character varying(50)  | 
 evaluationstatus | character varying(50)  | 
 transferdate     | date                   | 
 revenuetype      | character varying(50)  | 
 noofemployees    | integer                | 
 yahooid          | character varying(100) | 
 assignleadchk    | integer                | default 0
 department       | character varying(200) | 
 emailoptout      | character varying(3)   | default 0
 siccode          | character varying(50)  | 
Indexes:
    "leads_pkey" PRIMARY KEY, btree (leadid)
    "ftx_en_leads_company" gin (to_tsvector('v_en'::regconfig, for_fts(company::text)))
    "ftx_en_leads_email" gin (to_tsvector('v_en'::regconfig, for_fts(email::text)))
    "ftx_en_leads_emailoptout" gin (to_tsvector('v_en'::regconfig, for_fts(emailoptout::text)))
    "ftx_en_leads_firstname" gin (to_tsvector('v_en'::regconfig, for_fts(firstname::text)))
    "ftx_en_leads_lastname" gin (to_tsvector('v_en'::regconfig, for_fts(lastname::text)))
    "ftx_en_leads_yahooid" gin (to_tsvector('v_en'::regconfig, for_fts(yahooid::text)))
    "leads_converted_idx" btree (converted)
    "leads_leadsource_idx" btree (leadsource)
    "leads_leadstatus_idx" btree (leadstatus)



\d entity
                         Table "public.entity"
       Column       |            Type             |          Modifiers           
--------------------+-----------------------------+------------------------------
 id              | integer                     | not null
 smcreatorid        | integer                     | not null default 0
 smownerid          | integer                     | not null default 0
 modifiedby         | integer                     | not null default 0
 setype             | character varying(30)       | not null
 description        | text                        | 
 createdtime        | timestamp without time zone | not null
 modifiedtime       | timestamp without time zone | not null
 viewedtime         | timestamp without time zone | 
 status             | character varying(50)       | 
 version            | integer                     | not null default 0
 presence           | integer                     | default 1
 deleted            | integer                     | not null default 0
 owner_type         | character(1)                | not null default 'U'::bpchar
 last_activity_date | timestamp without time zone | 
Indexes:
    "entity_pkey" PRIMARY KEY, btree (id)
    "entity_createdtime_idx" btree (createdtime)
    "entity_modifiedby_idx" btree (modifiedby)
    "entity_modifiedtime_idx" btree (modifiedtime)
    "entity_setype_idx" btree (setype) WHERE deleted = 0
    "entity_smcreatorid_idx" btree (smcreatorid)
    "entity_smownerid_idx" btree (smownerid)
    "ftx_en_entity_description" gin (to_tsvector('v_en'::regconfig, for_fts(description)))
    "entity_deleted_idx" btree (deleted)
Referenced by:
    TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid) REFERENCES entity(id) ON DELETE CASCADE
    TABLE "servicecontracts" CONSTRAINT "fk_1_servicecontracts" FOREIGN KEY (servicecontractsid) REFERENCES entity(id) ON DELETE CASCADE
    TABLE "_emails" CONSTRAINT "fk__emails_id" FOREIGN KEY (id) REFERENCES entity(id) ON DELETE CASCADE
    

Please advice.

Thanks.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux