Hi, > -----Ursprüngliche Nachricht----- > Von: pgsql-performance-owner@xxxxxxxxxxxxxx > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] Im Auftrag > von Joost Kraaijeveld > Gesendet: Dienstag, 6. Dezember 2005 10:44 > An: Pgsql-Performance > Betreff: [PERFORM] Can this query go faster??? > SELECT customers.objectid FROM prototype.customers, > prototype.addresses WHERE customers.contactaddress = > addresses.objectid ORDER BY zipCode asc, housenumber asc > LIMIT 1 OFFSET 283745 > > Explain: > > Limit (cost=90956.71..90956.71 rows=1 width=55) > -> Sort (cost=90247.34..91169.63 rows=368915 width=55) > Sort Key: addresses.zipcode, addresses.housenumber > -> Hash Join (cost=14598.44..56135.75 rows=368915 width=55) > Hash Cond: ("outer".contactaddress = "inner".objectid) > -> Seq Scan on customers (cost=0.00..31392.15 > rows=368915 width=80) > -> Hash (cost=13675.15..13675.15 rows=369315 width=55) > -> Seq Scan on addresses (cost=0.00..13675.15 > rows=369315 width=55) > > The customers table has an index on contactaddress and objectid. > The addresses table has an index on zipcode+housenumber and objectid. The planner chooses sequential scans on customers.contactaddress and addresses.objectid instead of using the indices. In order to determine whether this is a sane decision, you should run EXPLAIN ANALYZE on this query, once with SET ENABLE_SEQSCAN = on; and once with SET ENABLE_SEQSCAN = off;. If the query is significantly faster with SEQSCAN off, then something is amiss - either you haven't run analyze often enough so the stats are out of date or you have random_page_cost set too high (look for the setting in postgresql.conf) - these two are the "usual suspects". Kind regards Markus
