Re: [PERFORM] Why so much time difference with a same query/plan?
Yann Michel [EMAIL PROTECTED] writes: On Wed, Dec 22, 2004 at 01:52:40PM -0800, Litao Wu wrote: Does the order of columns in the index matter since more than 50% customer_id = 158? I think it does not in Oracle. Will the performance be better if I change index xxx_idx to (domain, customer_id, created)? Well, in Oracle this would of cause matter. Oracle calculates index usage by being able to fill all index's attributes from the left to the right. If any one attribute within is missing Oracle would not test if it is only one attribute missing, or if all other attributes are missing within the query's where clause. This depends on the version of Oracle you're using. Oracle 9i introduced Index Skip Scans: http://www.oracle.com/technology//products/oracle9i/daily/apr22.html I don't know whether pg has something similar? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why so much time difference with a same query/plan?
On Sun, Dec 26, 2004 at 13:30:15 +0100, Karl Vogel [EMAIL PROTECTED] wrote: This depends on the version of Oracle you're using. Oracle 9i introduced Index Skip Scans: http://www.oracle.com/technology//products/oracle9i/daily/apr22.html I don't know whether pg has something similar? Postgres doesn't currently do this. There was some discussion about this not too long ago, but I don't think anyone indicated that they were going to work on it for 8.1. Postgres can use the leading part of a multikey index to start a scan, but it will just do a normal index scan with a filter. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Why so much time difference with a same query/plan?
Hi, On Wed, Dec 22, 2004 at 01:52:40PM -0800, Litao Wu wrote: Does the order of columns in the index matter since more than 50% customer_id = 158? I think it does not in Oracle. Will the performance be better if I change index xxx_idx to (domain, customer_id, created)? Well, in Oracle this would of cause matter. Oracle calculates index usage by being able to fill all index's attributes from the left to the right. If any one attribute within is missing Oracle would not test if it is only one attribute missing, or if all other attributes are missing within the query's where clause. Normaly you'd create an index using the most frequently parametrized attributes first, then the second ones and so on. If the usage isn't that different, you would use the most granule attribute in foremost followed by the second and so on. Regards, Yann ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
