X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2F8D69DC81E for ; Tue, 13 Dec 2005 07:37:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38088-01 for ; Tue, 13 Dec 2005 07:37:19 -0400 (AST) X-Greylist: delayed 03:16:20.941163 by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by postgresql.org (Postfix) with ESMTP id 28B8D9DC836 for ; Tue, 13 Dec 2005 07:37:14 -0400 (AST) Received: from mercure.sigma.fr (mercure.sigma.fr [195.25.81.9]) by svr2.postgresql.org (Postfix) with ESMTP id 29F82F0B89 for ; Tue, 13 Dec 2005 08:20:53 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mercure.sigma.fr (Postfix) with ESMTP id 25657911E for ; Tue, 13 Dec 2005 09:20:48 +0100 (CET) Received: from mercure.sigma.fr ([127.0.0.1]) by localhost (mercure-1 [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29971-09 for ; Tue, 13 Dec 2005 09:20:47 +0100 (CET) Received: from delpiv3000-124l (unknown [89.195.0.5]) by mercure.sigma.fr (Postfix) with ESMTP id E6406909F for ; Tue, 13 Dec 2005 09:20:47 +0100 (CET) Content-class: urn:content-classes:message From: Marc Cousin Organization: Sigma Informatique To: pgsql-performance@postgresql.org Subject: partitioning Date: Tue, 13 Dec 2005 09:20:47 +0100 User-Agent: KMail/1.9 MIME-Version: 1.0 Content-Type: Multipart/Mixed; boundary="Boundary-00=_fRonD9okC9edavU" Message-Id: <200512130920.47583.mcousin@sigma.fr> X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at sigma.fr X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.811 required=5 tests=[RCVD_IN_WHOIS_BOGONS=1.811, UPPERCASE_25_50=0] X-Spam-Score: 1.811 X-Spam-Level: * X-Archive-Number: 200512/309 X-Sequence-Number: 16130 This is a multi-part message in MIME format. --Boundary-00=_fRonD9okC9edavU Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, I've been working on trying to partition a big table (I've never partitioned a table in any other database till now). Everything went ok, except one query that didn't work afterwards. I've put the partition description, indexes, etc ..., and the explain plan attached. The query is extremely fast without partition (index scan backards on the primary key) The query is : "select * from logs order by id desc limit 100;" id is the primary key. It is indexed on all partitions. But the explain plan does full table scan on all partitions. While I think I understand why it is doing this plan right now, is there something that could be done to optimize this case ? Or put a warning in the docs about this kind of behaviour. I guess normally someone would partition to get faster queries :) Anyway, I thought I should mention this, as it has been quite a surprise. --Boundary-00=_fRonD9okC9edavU Content-Type: text/plain; charset="iso-8859-1"; name="partition.txt" Content-Transfer-Encoding: base64 Content-Description: partition.txt Content-Disposition: attachment; filename="partition.txt" Q1JFQVRFIFRBQkxFIGxvZ3NfMTUwIChDSEVDSyAoIGlkX21hY2hpbmUgPSAxNTApKSBJTkhFUklU UyAobG9ncykgVEFCTEVTUEFDRSBkYXRhX2xvZ3M7DQpDUkVBVEUgVEFCTEUgbG9nc18xNjMgKENI RUNLICggaWRfbWFjaGluZSA9IDE2MykpIElOSEVSSVRTIChsb2dzKSBUQUJMRVNQQUNFIGRhdGFf bG9nczsNCkNSRUFURSBUQUJMRSBsb2dzXzI4OSAoQ0hFQ0sgKCBpZF9tYWNoaW5lID0gMjg5KSkg SU5IRVJJVFMgKGxvZ3MpIFRBQkxFU1BBQ0UgZGF0YV9sb2dzOw0KQ1JFQVRFIFRBQkxFIGxvZ3Nf MzE5IChDSEVDSyAoIGlkX21hY2hpbmUgPSAzMTkpKSBJTkhFUklUUyAobG9ncykgVEFCTEVTUEFD RSBkYXRhX2xvZ3M7DQpDUkVBVEUgVEFCTEUgbG9nc18yMzggKENIRUNLICggaWRfbWFjaGluZSA9 IDIzOCkpIElOSEVSSVRTIChsb2dzKSBUQUJMRVNQQUNFIGRhdGFfbG9nczsNCkNSRUFURSBUQUJM RSBsb2dzXzE0OCAoQ0hFQ0sgKCBpZF9tYWNoaW5lID0gMTQ4KSkgSU5IRVJJVFMgKGxvZ3MpIFRB QkxFU1BBQ0UgZGF0YV9sb2dzOw0KQ1JFQVRFIFRBQkxFIGxvZ3NfMTc2IChDSEVDSyAoIGlkX21h Y2hpbmUgPSAxNzYpKSBJTkhFUklUUyAobG9ncykgVEFCTEVTUEFDRSBkYXRhX2xvZ3M7DQpDUkVB VEUgVEFCTEUgbG9nc18xNjQgKENIRUNLICggaWRfbWFjaGluZSA9IDE2NCkpIElOSEVSSVRTIChs b2dzKSBUQUJMRVNQQUNFIGRhdGFfbG9nczsNCkNSRUFURSBUQUJMRSBsb2dzXzMxNiAoQ0hFQ0sg KCBpZF9tYWNoaW5lID0gMzE2KSkgSU5IRVJJVFMgKGxvZ3MpIFRBQkxFU1BBQ0UgZGF0YV9sb2dz Ow0KQ1JFQVRFIFRBQkxFIGxvZ3NfMzEzIChDSEVDSyAoIGlkX21hY2hpbmUgPSAzMTMpKSBJTkhF UklUUyAobG9ncykgVEFCTEVTUEFDRSBkYXRhX2xvZ3M7DQpDUkVBVEUgVEFCTEUgbG9nc18yMTcg KENIRUNLICggaWRfbWFjaGluZSA9IDIxNykpIElOSEVSSVRTIChsb2dzKSBUQUJMRVNQQUNFIGRh dGFfbG9nczsNCkNSRUFURSBUQUJMRSBsb2dzXzE2NyAoQ0hFQ0sgKCBpZF9tYWNoaW5lID0gMTY3 KSkgSU5IRVJJVFMgKGxvZ3MpIFRBQkxFU1BBQ0UgZGF0YV9sb2dzOw0KQ1JFQVRFIFRBQkxFIGxv Z3NfMjg3IChDSEVDSyAoIGlkX21hY2hpbmUgPSAyODcpKSBJTkhFUklUUyAobG9ncykgVEFCTEVT UEFDRSBkYXRhX2xvZ3M7DQpDUkVBVEUgVEFCTEUgbG9nc18yNzkgKENIRUNLICggaWRfbWFjaGlu ZSA9IDI3OSkpIElOSEVSSVRTIChsb2dzKSBUQUJMRVNQQUNFIGRhdGFfbG9nczsNCkNSRUFURSBU QUJMRSBsb2dzXzI0OCAoQ0hFQ0sgKCBpZF9tYWNoaW5lID0gMjQ4KSkgSU5IRVJJVFMgKGxvZ3Mp IFRBQkxFU1BBQ0UgZGF0YV9sb2dzOw0KQ1JFQVRFIFRBQkxFIGxvZ3NfMjgwIChDSEVDSyAoIGlk X21hY2hpbmUgPSAyODApKSBJTkhFUklUUyAobG9ncykgVEFCTEVTUEFDRSBkYXRhX2xvZ3M7DQpD UkVBVEUgVEFCTEUgbG9nc18xNjUgKENIRUNLICggaWRfbWFjaGluZSA9IDE2NSkpIElOSEVSSVRT IChsb2dzKSBUQUJMRVNQQUNFIGRhdGFfbG9nczsNCkNSRUFURSBUQUJMRSBsb2dzXzMzMCAoQ0hF Q0sgKCBpZF9tYWNoaW5lID0gMzMwKSkgSU5IRVJJVFMgKGxvZ3MpIFRBQkxFU1BBQ0UgZGF0YV9s b2dzOw0KQ1JFQVRFIFRBQkxFIGxvZ3NfMTQ5IChDSEVDSyAoIGlkX21hY2hpbmUgPSAxNDkpKSBJ TkhFUklUUyAobG9ncykgVEFCTEVTUEFDRSBkYXRhX2xvZ3M7DQpDUkVBVEUgVEFCTEUgbG9nc18x NTcgKENIRUNLICggaWRfbWFjaGluZSA9IDE1NykpIElOSEVSSVRTIChsb2dzKSBUQUJMRVNQQUNF IGRhdGFfbG9nczsNCkNSRUFURSBUQUJMRSBsb2dzXzI3MiAoQ0hFQ0sgKCBpZF9tYWNoaW5lID0g MjcyKSkgSU5IRVJJVFMgKGxvZ3MpIFRBQkxFU1BBQ0UgZGF0YV9sb2dzOw0KQ1JFQVRFIFRBQkxF IGxvZ3NfMzExIChDSEVDSyAoIGlkX21hY2hpbmUgPSAzMTEpKSBJTkhFUklUUyAobG9ncykgVEFC TEVTUEFDRSBkYXRhX2xvZ3M7DQpDUkVBVEUgVEFCTEUgbG9nc18zMDkgKENIRUNLICggaWRfbWFj aGluZSA9IDMwOSkpIElOSEVSSVRTIChsb2dzKSBUQUJMRVNQQUNFIGRhdGFfbG9nczsNCkNSRUFU RSBUQUJMRSBsb2dzXzMxOCAoQ0hFQ0sgKCBpZF9tYWNoaW5lID0gMzE4KSkgSU5IRVJJVFMgKGxv Z3MpIFRBQkxFU1BBQ0UgZGF0YV9sb2dzOw0KDQoNCkNSRUFURSBVTklRVUUgSU5ERVggbG9nc18x NTBfcGtleSBPTiBsb2dzXzE1MCAoaWQpIFRBQkxFU1BBQ0UgaW5kZXhfbG9ncztDUkVBVEUgSU5E RVggZGF0ZV8xNTAgT04gbG9nc18xNTAgKGRhdGUpIFRBQkxFU1BBQ0UgaW5kZXhfbG9ncyA7Q1JF QVRFIElOREVYIGV2ZW50XzE1MCBPTiBsb2dzXzE1MCAoZXZlbmVtZW50KSBUQUJMRVNQQUNFIGlu ZGV4X2xvZ3M7DQoNCg0KLi4uLi4NCg0KDQpsb2dzPT4gZXhwbGFpbiBzZWxlY3QgKiBmcm9tIGxv Z3Mgb3JkZXIgYnkgaWQgZGVzYyBsaW1pdCAxMDA7DQogICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIFFVRVJZIFBMQU4NCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLQ0KIExpbWl0ICAoY29zdD0xNjUyNDY0Ny4yOS4uMTY1MjQ2NDcuNTQgcm93cz0xMDAg d2lkdGg9Mjk1KQ0KICAgLT4gIFNvcnQgIChjb3N0PTE2NTI0NjQ3LjI5Li4xNjU2ODM2Ny4xMSBy b3dzPTE3NDg3OTI3IHdpZHRoPTI5NSkNCiAgICAgICAgIFNvcnQgS2V5OiBwdWJsaWMubG9ncy5p ZA0KICAgICAgICAgLT4gIFJlc3VsdCAgKGNvc3Q9MC4wMC4uODI3NjIyLjI3IHJvd3M9MTc0ODc5 Mjcgd2lkdGg9Mjk1KQ0KICAgICAgICAgICAgICAgLT4gIEFwcGVuZCAgKGNvc3Q9MC4wMC4uODI3 NjIyLjI3IHJvd3M9MTc0ODc5Mjcgd2lkdGg9Mjk1KQ0KICAgICAgICAgICAgICAgICAgICAgLT4g IFNlcSBTY2FuIG9uIGxvZ3MgIChjb3N0PTAuMDAuLjgyNjIzMi43OCByb3dzPTE3NDUxOTc4IHdp ZHRoPTE2NSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBsb2dzXzE1MCBs b2dzICAoY29zdD0wLjAwLi4xOTkuMDQgcm93cz02MTA0IHdpZHRoPTE0NCkNCiAgICAgICAgICAg ICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBsb2dzXzE2MyBsb2dzICAoY29zdD0wLjAwLi4yNjEu Nzkgcm93cz03MDc5IHdpZHRoPTE2OSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2Nh biBvbiBsb2dzXzI4OSBsb2dzICAoY29zdD0wLjAwLi40MjguOTMgcm93cz0xMDY5MyB3aWR0aD0y MDApDQogICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbG9nc18zMTkgbG9ncyAg KGNvc3Q9MC4wMC4uMzEuOTIgcm93cz05OTIgd2lkdGg9MTI5KQ0KICAgICAgICAgICAgICAgICAg ICAgLT4gIFNlcSBTY2FuIG9uIGxvZ3NfMjM4IGxvZ3MgIChjb3N0PTAuMDAuLjI4LjAxIHJvd3M9 NzAxIHdpZHRoPTE5OSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBsb2dz XzE0OCBsb2dzICAoY29zdD0wLjAwLi44MC4xNSByb3dzPTIwMTUgd2lkdGg9MTk1KQ0KICAgICAg ICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIGxvZ3NfMTc2IGxvZ3MgIChjb3N0PTAuMDAu LjEyLjQwIHJvd3M9MjQwIHdpZHRoPTI5NSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEg U2NhbiBvbiBsb2dzXzE2NCBsb2dzICAoY29zdD0wLjAwLi4xMi40MCByb3dzPTI0MCB3aWR0aD0y OTUpDQogICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbG9nc18zMTYgbG9ncyAg KGNvc3Q9MC4wMC4uMTIuNDAgcm93cz0yNDAgd2lkdGg9Mjk1KQ0KICAgICAgICAgICAgICAgICAg ICAgLT4gIFNlcSBTY2FuIG9uIGxvZ3NfMzEzIGxvZ3MgIChjb3N0PTAuMDAuLjEyLjQwIHJvd3M9 MjQwIHdpZHRoPTI5NSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBsb2dz XzIxNyBsb2dzICAoY29zdD0wLjAwLi4xMi40MCByb3dzPTI0MCB3aWR0aD0yOTUpDQogICAgICAg ICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbG9nc18xNjcgbG9ncyAgKGNvc3Q9MC4wMC4u NTcuMzYgcm93cz0xNTM2IHdpZHRoPTE3MCkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEg U2NhbiBvbiBsb2dzXzI4NyBsb2dzICAoY29zdD0wLjAwLi4xMi40MCByb3dzPTI0MCB3aWR0aD0y OTUpDQogICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbG9nc18yNzkgbG9ncyAg KGNvc3Q9MC4wMC4uMTIuNDAgcm93cz0yNDAgd2lkdGg9Mjk1KQ0KICAgICAgICAgICAgICAgICAg ICAgLT4gIFNlcSBTY2FuIG9uIGxvZ3NfMjQ4IGxvZ3MgIChjb3N0PTAuMDAuLjEyLjQwIHJvd3M9 MjQwIHdpZHRoPTI5NSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBsb2dz XzI4MCBsb2dzICAoY29zdD0wLjAwLi4xMi40MCByb3dzPTI0MCB3aWR0aD0yOTUpDQogICAgICAg ICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbG9nc18xNjUgbG9ncyAgKGNvc3Q9MC4wMC4u MzQuMDkgcm93cz05MDkgd2lkdGg9MTY4KQ0KICAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBT Y2FuIG9uIGxvZ3NfMzMwIGxvZ3MgIChjb3N0PTAuMDAuLjYyLjQ2IHJvd3M9MTc0NiB3aWR0aD0x NjApDQogICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbG9nc18xNDkgbG9ncyAg KGNvc3Q9MC4wMC4uMTIuNDAgcm93cz0yNDAgd2lkdGg9Mjk1KQ0KICAgICAgICAgICAgICAgICAg ICAgLT4gIFNlcSBTY2FuIG9uIGxvZ3NfMTU3IGxvZ3MgIChjb3N0PTAuMDAuLjEyLjQwIHJvd3M9 MjQwIHdpZHRoPTI5NSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBsb2dz XzI3MiBsb2dzICAoY29zdD0wLjAwLi4zMi4xNCByb3dzPTgxNCB3aWR0aD0xODMpDQogICAgICAg ICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbG9nc18zMTEgbG9ncyAgKGNvc3Q9MC4wMC4u MTIuNDAgcm93cz0yNDAgd2lkdGg9Mjk1KQ0KICAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBT Y2FuIG9uIGxvZ3NfMzA5IGxvZ3MgIChjb3N0PTAuMDAuLjEyLjQwIHJvd3M9MjQwIHdpZHRoPTI5 NSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBsb2dzXzMxOCBsb2dzICAo Y29zdD0wLjAwLi4xMi40MCByb3dzPTI0MCB3aWR0aD0yOTUpDQoNCg== --Boundary-00=_fRonD9okC9edavU--