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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
