[postgis-users] OT Understanding slow queries

[Ben Madin]

I'm no expert at this, but my understanding (which is limited) was that you are asking for the whole table, so indexing doesn't really get used (my understanding is that indexing helps to find the page for a subset of data more quickly than scanning through the whole lot).

Also, you might be able to get some speed up by using a different join type (outer join and not null where clause)?

cheers

Ben


On 25/08/2011, at 9:41 PM, Charles Galpin wrote:

> If this is too off topic, please let me know and I'll sign up on a postgres list to get help. But this is related to my use of postgis and If anyone knows this stuff, it's you guys.
> 
> I have an example query that I expect to be much faster, but my main concern is we are about to do some visualization of historical congestion data which will require queries across much larger data sets - like 150 million records a day. We are about to test using partitions but the number per table will still be much larger than what I am dealing with now.
> 
> So here is a query I would think would be much faster than 43 seconds for two tables, one with about 97k rows, and the other 3.2 million.
> 
> explain select count(l.*) 
> from links l, source_link ld where l.link_id = ld.link_id;
> /*
> 'Aggregate  (cost=174731.72..174731.73 rows=1 width=32)'
> '  ->  Hash Join  (cost=13024.27..166784.14 rows=3179029 width=32)'
> '        Hash Cond: (ld.link_id = l.link_id)'
> '        ->  Seq Scan on source_link ld  (cost=0.00..58282.29 rows=3179029 width=10)'
> '        ->  Hash  (cost=10963.12..10963.12 rows=96812 width=42)'
> '              ->  Seq Scan on links l  (cost=0.00..10963.12 rows=96812 width=42)'
> */
> 
> Each table has an index on link_id, defined like this
> 
> CREATE INDEX links_link_id_idx
>  ON links
>  USING btree
>  (link_id);
> 
> CREATE INDEX source_link_link_id_idx
>  ON source_link
>  USING btree
>  (link_id);
> 
> Shouldn't this index prevent these sequential scans, or am I misreading this?  Should this really take 43 seconds?
> 
> thanks for any advice,
> charles
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users