In bug #6351 it's pointed out that this fails unexpectedly:
CREATE TABLE tab (id SERIAL, a INTEGER, b INTEGER);
CREATE INDEX tab123 ON tab (a, b, a);
SELECT a, b FROM tab WHERE a = 0 AND b = 1;
ERROR: btree index keys must be ordered by attribute
I looked into this a bit and find that indxpath.c
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> How would this affect changing the type of a column?
It doesn't, because we drop and rebuild indexes completely during ALTER
COLUMN TYPE.
regards, tom lane
---(end of broadcast)--
On Thu, Jan 19, 2006 at 02:46:11AM -0500, Tom Lane wrote:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Tom Lane <[EMAIL PROTECTED]> writes:
> >> But you couldn't make any meaningful changes in the definition of an
> >> index, such as changing its column set, operator classes, partial-index
> >> pre
On Thu, Jan 19, 2006 at 02:46:11AM -0500, Tom Lane wrote:
> The concerns that I find more interesting are changes in the underlying
> objects. We don't have an ALTER OPERATOR CLASS, much less an ALTER
> ACCESS METHOD, but it's certainly theoretically possible to change the
> definition of a suppor
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> But you couldn't make any meaningful changes in the definition of an
>> index, such as changing its column set, operator classes, partial-index
>> predicate, etc, except by dropping and recreating it.
> The only examp
Tom Lane <[EMAIL PROTECTED]> writes:
> This would still support REINDEX (which changes pg_class.relfilenode in
> order to replace the physical file) and ALTER INDEX SET TABLESPACE.
> But you couldn't make any meaningful changes in the definition of an
> index, such as changing its column set, ope
I've been working on getting the system to pass regression tests cleanly
when forcing a cache flush at every possible instant. The main tests
pass now (in 8.1 --- HEAD remains broken pending lookup_rowtype_tupdesc
fix), but contrib is still crashing. On investigation the problem turns
out to be i
On Mon, Aug 22, 2005 at 04:01:04PM -0400, Tom Lane wrote:
> Joshua N Pritikin <[EMAIL PROTECTED]> writes:
> > Is anybody working on allowing indexes to span multiple tables?
> > IF not, I'll give it a try.
>
> Wouldn't recommend it as a project for a beginning backend hacker;
> the locking conside
On E, 2005-08-22 at 16:01 -0400, Tom Lane wrote:
> Joshua N Pritikin <[EMAIL PROTECTED]> writes:
> > Is anybody working on allowing indexes to span multiple tables?
> > IF not, I'll give it a try.
>
> Wouldn't recommend it as a project for a beginning backend hacker;
> the locking considerations a
Joshua N Pritikin <[EMAIL PROTECTED]> writes:
> Is anybody working on allowing indexes to span multiple tables?
> IF not, I'll give it a try.
Wouldn't recommend it as a project for a beginning backend hacker;
the locking considerations alone are a bit daunting.
See the archives for prior discussi
Is anybody working on allowing indexes to span multiple tables?
IF not, I'll give it a try.
--
Make April 15 just another day, visit http://fairtax.org
signature.asc
Description: Digital signature
On Thu, 16 Jan 2003, Tom Lane wrote:
> > mlw said:
> >>> Does anyone think it would be a good idea, or is it even practical, to
> >>> have a 'indx' subdirectory along side of the 'base' directory?
> >>>
> >>> I was thinking that, if it were an easy modification, that it could be
> >>> an easy w
> mlw said:
>>> Does anyone think it would be a good idea, or is it even practical, to
>>> have a 'indx' subdirectory along side of the 'base' directory?
>>>
>>> I was thinking that, if it were an easy modification, that it could be
>>> an easy way to separate data and indexes to different hard
Does anyone think it would be a good idea, or is it even practical, to
have a 'indx' subdirectory along side of the 'base' directory?
I was thinking that, if it were an easy modification, that it could be
an easy way to separate data and indexes to different hard disks.
---
I'm trying to work out what to do with indexes in the context of
schemas.
As of today's CVS tip, what the code does is that CREATE INDEX can only
specify an unqualified index name, and the index is automatically
created in the same namespace as its parent table. Thus, index names
still have to b
> Is there any possibility of overlapping rows between the parts of the
> union? If not, I'd suggest union all, since that might get rid of the top
> level unique and sort steps (probably not a huge gain, but might help).
Hi,
thanx for the response, there is a little possibility of overlapping
On Fri, 27 Jul 2001, Robert Vojta wrote:
> netacc=> EXPLAIN (SELECT SUM(counterfrom) AS from, SUM(counterto) AS to,
> floor((985098900 - date_part('epoch', counterstamp)) / 300) AS sequence
> FROM counters WHERE line='absolonll' AND date_part('epoch', counterstamp)
> > 984978900 GROUP BY sequence
> The index is only used for the line= part of the where clause
> with your query. With many rows the "line=" is not selective enough
> to justify the index.
I tried move only needed data into new table and change query into ...
netacc=> EXPLAIN SELECT counterfrom AS from, counterto AS to,
fl
> The index is only used for the line= part of the where clause
> with your query. With many rows the "line=" is not selective enough
> to justify the index.
Hi,
I tried you suggestion about 'AND counterstamp > '2001-07-26 00:00:00.0' and
it works and index is used :) But, whole query run for
> I would guess the problem is the restriction on counterstamp, because
> written like that, it probably can't use the index.
>
> try something where you avoid the use of the date_part function e.g.:
> AND counterstamp > '2001-07-26 00:00:00.0'
I will try it, but it use the index when t
> netacc=> EXPLAIN (SELECT SUM(counterfrom) AS from,
> SUM(counterto) AS to,
> floor((985098900 - date_part('epoch', counterstamp)) / 300)
> AS sequence
> FROM counters WHERE line='absolonll' AND date_part('epoch',
> ) counterstamp > 984978900 GROUP BY sequence, line) ...
I would guess the pr
Hi,
I'm fighting with problem with indexes. I read documentation about
performance tips, about internal logic functions which are making decision
if to use or not use indexes, etc. and I'm still failed. I'm not SQL
guru and I don't know what to do now. My tables and indexes looks like
...
CREAT
> url_idx seems OK:
>
> logger=# EXPLAIN SELECT * FROM access_log WHERE url_id = 1000;
> Index Scan using url_idx on access_log
> (cost=0.00..3618.92 rows=1002 width=89)
>
>
>
> But the others not:
>
> logger=# EXPLAIN SELECT * FROM access_log WHERE method_num = 0;
> Seq Scan
Hi,
I have the following table, containing about 57 Rows, but some
indexes are not used, on 7.1RC4, freshly vacuumed (analyse). It was the
same at least in 7.1RC1
--- SNIP ---
CREATE TABLE access_log(
site_id int2NOT NULL DEFAULT 0,
access_time timestam
24 matches
Mail list logo
