pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
low cardinality column
5+ messages / 5 participants
[nested] [flat]

* low cardinality column
@ 2003-10-02 17:03 rwu.cbnco.com@cbnco.com
  0 siblings, 0 replies; 5+ messages in thread

From: rwu.cbnco.com@cbnco.com @ 2003-10-02 17:03 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have a select like this:

SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0;

in the query:
transactionid is the primary key of cbntransaction table,
But transactiontypeid is a low cardinality column, there're over 100,000
records has the same trnsactiontypeid.
I was trying to create an index on (transactiontypeid, transactionid), but
no luck on that, postgresql will still scan the table.
I'm wondering if there's solution for this query:
Maybe something like if I can partition the table using transactiontypeid,
and do a local index on transactionid on each partition, but I couldnt'
find any doc on postgresql to do that.

Thanks in advance,
rong :-)





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* low cardinality column
@ 2003-10-02 18:30 Rong Wu <rwu@cbnco.com>
  2003-10-02 18:37 ` Re: low cardinality column Josh Berkus <josh@agliodbs.com>
  2003-10-02 18:50 ` Re: low cardinality column Rod Taylor <rbt@rbt.ca>
  0 siblings, 2 replies; 5+ messages in thread

From: Rong Wu @ 2003-10-02 18:30 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have a select like this:

SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0;

in the query:
transactionid is the primary key of cbntransaction table,
But transactiontypeid is a low cardinality column, there're over 100,000
records has the same trnsactiontypeid.
I was trying to create an index on (transactiontypeid, transactionid), but
no luck on that, postgresql will still scan the table.
I'm wondering if there's solution for this query:
Maybe something like if I can partition the table using transactiontypeid,
and do a local index on transactionid on each partition, but I couldnt'
find any doc on postgresql to do that.

Thanks in advance,
rong :-)





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: low cardinality column
  2003-10-02 18:30 low cardinality column Rong Wu <rwu@cbnco.com>
@ 2003-10-02 18:37 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2003-10-02 18:37 UTC (permalink / raw)
  To: rwu@cbnco.com; pgsql-performance

Rong,

> I have a select like this:
> 
> SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0;

Simple workaround:

Create an mulit-column index on transactiontypeid, transactionid.

SELECT transactionid FROM cbtransaction 
WHERE transactiontypeid=0
ORDER BY transactionid DESC LIMIT 1;

This approach will use the index.

Of course, if the reason you are selecting the max id is to get the next id, 
there are much better ways to do that.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: low cardinality column
  2003-10-02 18:30 low cardinality column Rong Wu <rwu@cbnco.com>
@ 2003-10-02 18:50 ` Rod Taylor <rbt@rbt.ca>
  2003-10-02 19:00   ` Re: low cardinality column Bill Moran <wmoran@potentialtech.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Rod Taylor @ 2003-10-02 18:50 UTC (permalink / raw)
  To: rwu@cbnco.com; +Cc: pgsql-performance

On Thu, 2003-10-02 at 14:30, Rong Wu wrote:
> Hi,
> 
> I have a select like this:
> 
> SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0;

For various reasons (primarily MVCC and the ability to make custom
aggregates making it difficult)  MAX() is not optimized in this fashion.

Try:

  SELECT transactionid
    FROM ...
   WHERE ...
ORDER BY transactionid DESC
   LIMIT 1;

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: low cardinality column
  2003-10-02 18:30 low cardinality column Rong Wu <rwu@cbnco.com>
  2003-10-02 18:50 ` Re: low cardinality column Rod Taylor <rbt@rbt.ca>
@ 2003-10-02 19:00   ` Bill Moran <wmoran@potentialtech.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Bill Moran @ 2003-10-02 19:00 UTC (permalink / raw)
  To: ; +Cc: pgsql-performance

Rod Taylor wrote:
> On Thu, 2003-10-02 at 14:30, Rong Wu wrote:
> 
>>Hi,
>>
>>I have a select like this:
>>
>>SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0;
> 
> 
> For various reasons (primarily MVCC and the ability to make custom
> aggregates making it difficult)  MAX() is not optimized in this fashion.
> 
> Try:
> 
>   SELECT transactionid
>     FROM ...
>    WHERE ...
> ORDER BY transactionid DESC
>    LIMIT 1;

Despite this good suggestion, if you're using this technique to generate
the next transaction ID, you're going to have errors as concurrency rises.

Use a SERIAL, which guarantees that you won't have two processes generate
the same number.

-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-10-02 19:00 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-02 17:03 low cardinality column rwu.cbnco.com@cbnco.com
2003-10-02 18:30 low cardinality column Rong Wu <rwu@cbnco.com>
2003-10-02 18:37 ` Josh Berkus <josh@agliodbs.com>
2003-10-02 18:50 ` Rod Taylor <rbt@rbt.ca>
2003-10-02 19:00   ` Bill Moran <wmoran@potentialtech.com>

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