[postgis-users] Slow select from big table

[Obe, Regina]

I know in prior versions of Postgresql, select count(*) was slow.  I think it has improved, but may be still slower than doing a count of a specific field.

Have you tried doing something like

select count(gid) from sometable 


to see if that is any faster where gid is a field in the table that is guaranteed not to be null or a primary key. 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Matt Doughty
Sent: Wednesday, June 06, 2007 11:57 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Slow select from big table

Version 1.6.3.

Alter carrying out a full vacuum, the same query took ten seconds more. I'm going to try a vacuum analyse now, then the scripts I've been sent.

Matt

 
 
Matt Doughty
 
GEOGRAMA S.L.
Tel.:  +34 945 13 13 72    652 77 14 15
Fax: +34 945 23 03 40 
www.geograma.com
 
 

-----Mensaje original-----
De: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] En nombre de Gerry Creager
Enviado el: miércoles, 06 de junio de 2007 17:54
Para: PostGIS Users Discussion
Asunto: Re: [postgis-users] Slow select from big table

What version of the database code are you running?

volunteer at spatiallink.org wrote:
> what if
> select count(*) from table where true;
> ??
> 
> sincerely
> siva
> 
> -------- Original Message --------
> Subject: RE: [postgis-users] Slow select from big table
> From: "Matt Doughty" <matt.doughty at geograma.com>
> Date: Wed, June 06, 2007 11:30 am
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> 
> I'm trying out a vacuum at the moment to see if it helps.       Matt    
>            Matt Doughty     GEOGRAMA S.L.   Tel.:  +34 945 13 13 72   
> 652 77 14 15   Fax: +34 945 23 03 40    www.geograma.com
>      
> De: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] En nombre de Rob
> Tester
> Enviado el: miércoles, 06 de junio de 2007 17:23
> Para: 'PostGIS Users Discussion'
> Asunto: RE: [postgis-users] Slow select from big table
> 
> Sounds more like a question for the general group.  But, have you run a
> vacuum analyze on the table ?
> 
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Matt Doughty
> Sent: Wednesday, June 06, 2007 8:19 AM
> To: postgis-users at postgis.refractions.net
> Cc: Sergio Jorrín Abellán
> Subject: [postgis-users] Slow select from big table
> 
> Hi List,   I've got a simple question, how can I speed up this query?   
> SELECT COUNT (*) FROM table1   Currently it's taking a little more than
> five minutes to run on a table with 3.4 m rows. The table is gist
> indexed. Is it normal that Postgres takes so long?   Cheers,   Matt    
>           Matt Doughty     GEOGRAMA S.L.   Tel.:  +34 945 13 13 72   
> 652 77 14 15   Fax: +34 945 23 03 40    www.geograma.com
>   
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> ------------------------------------------------------------------------
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
Gerry Creager -- gerry.creager at tamu.edu
Texas Mesonet -- AATLT, Texas A&M University
Cell: 979.229.5301 Office: 979.458.4020 FAX: 979.862.3983
Office: 1700 Research Parkway Ste 160, TAMU, College Station, TX 77843


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.