From: Jim C. Nasby <jnasby@pervasive.com>
To: Theo Kramer <theo@flame.co.za>
Cc: pgsql-performance@postgresql.org
Subject: Re: Multicolumn order by
Date: Tue, 18 Apr 2006 18:13:53 -0500
Message-ID: <20060418231352.GM49405@pervasive.com> (raw)
In-Reply-To: <1145398075.3048.18.camel@josh>
References: <1145398075.3048.18.camel@josh>
Assuming stats are accurate, you're reading through 5.5M index rows in
order to run that limit query. You didn't say what the index was
actually on, but you might want to try giving each column it's own
index. That might make a bitmap scan feasable.
I know this doesn't help right now, but 8.2 will also allow you to do
this using a row comparitor. You might want to compile cvs HEAD and see
how that does with this query (specifically if using a row comparitor
performs better than the query below).
On Wed, Apr 19, 2006 at 12:07:55AM +0200, Theo Kramer wrote:
> Hi
>
> Apologies if this has already been raised...
>
> PostgreSQL 8.1.3 and prior versions. Vacuum done.
>
> Assuming a single table with columns named c1 to cn and a requirement to
> select from a particular position in multiple column order.
>
> The column values in my simple example below denoted by 'cnv' a typical
> query would look as follows
>
> select * from mytable where
> (c1 = 'c1v' and c2 = 'c2v' and c3 >= 'c3v') or
> (c1 = 'c1v' and c2 > 'c2v') or
> (c1 > 'c1v')
> order by c1, c2, c3;
>
> In real life with the table containing many rows (>9 Million) and
> a single multicolumn index on the required columns existing I get the
> following
>
> explain analyse
> SELECT
> tran_subledger,
> tran_subaccount,
> tran_mtch,
> tran_self,
> tran_Rflg FROM tran
> WHERE ((tran_subledger = 2 AND tran_subaccount = 'ARM '
> AND tran_mtch = 0 AND tran_self >= 0 )
> OR (tran_subledger = 2 AND tran_subaccount = 'ARM ' AND
> tran_mtch > 0 )
> OR (tran_subledger = 2 AND tran_subaccount > 'ARM ' )
> OR (tran_subledger > 2 ))
> ORDER BY tran_subledger,
> tran_subaccount,
> tran_mtch,
> tran_self
> limit 10;
>
> Limit (cost=0.00..25.21 rows=10 width=36) (actual
> time=2390271.832..2390290.305 rows=10 loops=1)
> -> Index Scan using tran_mtc_idx on tran (cost=0.00..13777295.04
> rows=5465198 width=36) (actual time=2390271.823..2390290.252 rows=10
> loops=1)
> Filter: (((tran_subledger = 2) AND (tran_subaccount = 'ARM
> '::bpchar) AND (tran_mtch = 0) AND (tran_self >= 0)) OR ((tran_subledger
> = 2) AND (tran_subaccount = 'ARM '::bpchar) AND
> (tran_mtch > 0)) OR ((tran_subledger = 2) AND (tran_subaccount >
> 'ARM '::bpchar)) OR (tran_subledger > 2))
> Total runtime: 2390290.417 ms
>
> Any suggestions/comments/ideas appreciated.
> --
> Regards
> Theo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, theo@flame.co.za
Subject: Re: Multicolumn order by
In-Reply-To: <20060418231352.GM49405@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox