X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C387553690 for ; Wed, 20 Apr 2005 11:35:00 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33516-08 for ; Wed, 20 Apr 2005 14:34:56 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 1D787536AD for ; Wed, 20 Apr 2005 11:34:54 -0300 (ADT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j3KEYuDC021367; (envelope-from ) Wed, 20 Apr 2005 09:34:56 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j3KEYseW028243 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 20 Apr 2005 09:34:55 -0500 (CDT) Message-ID: <42666890.1000401@arbash-meinel.com> Date: Wed, 20 Apr 2005 09:34:56 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard van den Berg Cc: pgsql-perform Subject: Re: When are index scans used over seq scans? References: <426665A0.1080102@trust-factory.com> In-Reply-To: <426665A0.1080102@trust-factory.com> X-Enigmail-Version: 0.91.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigB8AC96733943C648CF833E75" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/497 X-Sequence-Number: 11894 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigB8AC96733943C648CF833E75 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Richard van den Berg wrote: >We have a table with 1M rows that contain sessions with a start and >finish timestamps. When joining this table with a 10k table with rounded >timestamps, explain shows me sequential scans are used, and the join >takes about 6 hours (2s per seq scan on session table * 10000): > > Nested Loop (cost=252.80..233025873.16 rows=1035480320 width=97) >Join Filter: (("outer".starttime <= "inner".ts) AND ("outer".finishtime > > >>= "inner".ts)) >> >> > -> Seq Scan on sessions us (cost=0.00..42548.36 rows=924536 >width=105) -> Materialize (cost=252.80..353.60 rows=10080 width=8) > -> Seq Scan on duration du (cost=0.00..252.80 rows=10080 width=8) > >However, during the initial loading of the data (we first load into text >tables, then convert to tables using timestamps etc, then run this >query) the same query took only 12 minutes. While debugging, I increased >cpu_tuple_cost to 0.1 (from 0.01). Now the explain shows an index scan, >and the run time comes down to 11 minutes: > > Nested Loop (cost=0.00..667700310.42 rows=1035480320 width=97) > -> Seq Scan on sessions us (cost=0.00..125756.60 rows=924536 width=105) > -> Index Scan using ix_du_ts on duration du (cost=0.00..604.46 >rows=1120 width=8) > Index Cond: (("outer".starttime <= du.ts) AND >("outer".finishtime >= du.ts)) > >I am glad that I found a way to force the use of the index, but still >can't explain why in the initial run the planner made the right choice, >but now I need to give it a hand. Could this have to do with the >statistics of the tables? I make very sure (during the initial load and >while testing) that I vacuum analyze all tables after I fill them. > >I'm runing postgres 7.4.7. > >Any help is appreciated. > > > I believe the problem is that postgres doesn't recognize how restrictive a date-range is unless it uses constants. So saying: select blah from du WHERE time between '2004-10-10' and '2004-10-15'; Will properly use the index, because it realizes it only returns a few rows. However select blah from du, us where du.ts between us.starttime and us.finishtime; Doesn't know how selective that BETWEEN is. This has been discussed as a future improvement to the planner (in 8.*). I don't know the current status. Also, in the future, you really should post your table schema, and explain analyze instead of just explain. (I realize that with a 6hr query it is a little painful.) Notice that in the above plans, the expected number of rows drops from 10k down to 1k (which is probably where the planner decides to switch). And if you actually did the analyze probably the number of rows is much lower still. Probably you should try to find out the status of multi-table selectivity. It was discussed in the last couple of months. John =:-> --------------enigB8AC96733943C648CF833E75 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCZmiQJdeBCYSNAAMRAn3mAKDARvtifbQlcn3FVJWVMWy98AfOjACgyPRa ZDVmbjdr2LNnUNjw/Nb1Jo4= =Fpxr -----END PGP SIGNATURE----- --------------enigB8AC96733943C648CF833E75--