From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Stapleton <alexs@advfn.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL using the wrong Index
Date: Mon, 13 Jun 2005 10:54:52 -0400
Message-ID: <19854.1118674492@sss.pgh.pa.us> (raw)
In-Reply-To: <2D1A68BF-B3A4-490E-8053-3CF6626B8DAF@advfn.com>
References: <2D1A68BF-B3A4-490E-8053-3CF6626B8DAF@advfn.com>
Alex Stapleton <alexs@advfn.com> writes:
> l1_historical=# \d "N_intra_pkey"
> Index "N_intra_pkey"
> Column | Type
> --------+-----------------------------
> symbol | text
> time | timestamp without time zone
> unique btree (primary key)
> and on queries like this
> select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST'
> order by time desc limit 1;
This was just covered in excruciating detail yesterday ...
You need to write
order by symbol desc, time desc limit 1
to get the planner to recognize the connection to the sort order
of this index. Since you're only selecting one value of symbol,
the actual output doesn't change.
> Oh, we are running 7.4.2 btw. And our random_page_cost = 1
I'll bet lunch that that is a bad selection of random_page_cost,
unless your database is so small that it all fits in RAM.
regards, tom lane
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: tgl@sss.pgh.pa.us, alexs@advfn.com
Subject: Re: PostgreSQL using the wrong Index
In-Reply-To: <19854.1118674492@sss.pgh.pa.us>
* 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