On 3/31/2015 3:28 PM, Fabio Ugo Venchiarutti wrote:
On 01/04/15 06:12, Andy Colson wrote:
On 3/31/2015 1:58 AM, Fabio Ugo Venchiarutti wrote:
Greetings
I'm working for a startup and our core DB is growing rather fast.
Our target scale is large enough that we expect some of our core tables'
On 3/31/2015 1:58 AM, Fabio Ugo Venchiarutti wrote:
Greetings
I'm working for a startup and our core DB is growing rather fast.
Our target scale is large enough that we expect some of our core tables'
indexes to grow bigger than the memory on any single node over the next
couple years (our cur
Ruben Blanco writes:
> I'm trying to reduce execution time on a query using a partial index,
> but Postgres doesn't make a significant improvement, even when the
> partial index is 30 times smaller than the index used currently.
That doesn't really matter that much. The part of the index a given
Hi, folks:
I'm trying to reduce execution time on a query using a partial index,
but Postgres doesn't make a significant improvement, even when the
partial index is 30 times smaller than the index used currently. Query
plan returns a slightly higher cost (cost=0.00..327952.12) for the
partial inde
I wrote:
> "David Johnston" writes:
>> If I explain the above query multiple times (without any data changes) I get
>> either of the two query plans / the Bitmap Heap one more often. If I
>> analyze the table I immediately get the "Index Scan" plan first but
>> subsequent explains revert to switc
"David Johnston" writes:
> If I explain the above query multiple times (without any data changes) I get
> either of the two query plans / the Bitmap Heap one more often. If I
> analyze the table I immediately get the "Index Scan" plan first but
> subsequent explains revert to switching between th
Hi,
I have the following query:
EXPLAIN SELECT * FROM taskinstance WHERE ti_status IN ('ACTIVE',
'DISPATCHED', 'FAILURE');
With two indices:
ti_active. partial index over (ti_status) WHERE ti_status IN
('ACTIVE','DISPATCHED','FAILURE')
ti_sortedstatus. full index over (ti_status, ti_c
- On *Thu, 6/26/08, Phillip Mills <[EMAIL PROTECTED]>* wrote:
>
> From: Phillip Mills <[EMAIL PROTECTED]>
> Subject: [GENERAL] Partial Index Too Literal?
> To: pgsql-general@postgresql.org
> Date: Thursday, June 26, 2008, 7:24 PM
>
>
> Under somewhat unusual circu
use this
explain analyze select * from result where active = 't';
--- On Thu, 6/26/08, Phillip Mills <[EMAIL PROTECTED]> wrote:
From: Phillip Mills <[EMAIL PROTECTED]>
Subject: [GENERAL] Partial Index Too Literal?
To: pgsql-general@postgresql.org
Date: Thursday, June 26
On Thu, Jun 26, 2008 at 03:24:41PM -0400, Phillip Mills wrote:
> dev=# explain analyze select * from result where active = true;
> dev=# explain analyze select * from result where active is true;
> This is version 8.2.6. Is there something I'm missing that could make these
> queries ever produce
Under somewhat unusual circumstances, rows in one of our tables have an
'active' flag with a true value. We check for these relatively often since
they represent cases that need special handling. We've found through
testing that having a partial index on that field works well. What seems
odd to
On Wednesday 12 September 2007 09:34:55 Richard Huxton wrote:
> To be honest, I'd probably just have a separate column "uid_type", set
> it when creating the user and then just have a partial index WHERE
> uid_type='IP'
Or have a separate column with the user ID and have "(anonymous)" when you
h
Phoenix Kiula wrote:
Ok, I've hit a snag about this index. I think it's to do with how my
regex is structured. Basically this column can have either IP
addresses, or alphanumeric user IDs. If it is not an IP address, it is
a registered user ID. What is the best way of ascertaining that a
column
On 11/09/2007, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
>
> The suggestion in this thread that a regex index will come into play
> only when the WHERE condition specifically mentions it was indeed the
> key for me.
Ok, I've hit a snag about this index. I think it's to do with how my
regex is str
On 11/09/2007, Tom Lane <[EMAIL PROTECTED]> wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > The planner isn't smart enough to figure out which queries can use this
> > index by examining them, it just looks for (NOT paid) in the WHERE
> > clause and if it doesn't find it, ignores the index.
Richard Huxton <[EMAIL PROTECTED]> writes:
> The planner isn't smart enough to figure out which queries can use this
> index by examining them, it just looks for (NOT paid) in the WHERE
> clause and if it doesn't find it, ignores the index.
Well, it's a little bit brighter than that: it has some
Richard Broersma Jr wrote:
--- Richard Huxton <[EMAIL PROTECTED]> wrote:
Phoenix Kiula wrote:
CREATE INDEX idx_trades_tid_partial ON trades (trader_id) WHERE
trader_id ~ '[a-z]' ; WHERE trader_id = 'johndoe'
It is not using this index at all! It is using no index in fact,
it's trying to do a s
--- Richard Huxton <[EMAIL PROTECTED]> wrote:
> Phoenix Kiula wrote:
> > CREATE INDEX idx_trades_tid_partial ON trades (trader_id)
> > WHERE trader_id ~ '[a-z]' ;
>
> >WHERE trader_id = 'johndoe'
> >
> > It is not using this index at all! It is using no index in fact, it's
> > trying to d
Phoenix Kiula wrote:
CREATE INDEX idx_trades_tid_partial ON trades (trader_id)
WHERE trader_id ~ '[a-z]' ;
WHERE trader_id = 'johndoe'
It is not using this index at all! It is using no index in fact, it's
trying to do a sequential scan. Any ideas why this partial index is
not working??
I have a varchar ID field which captures a user account. If there is
no user id, then we just store the IP address of the user. Realizing
that we would like to index only those tuples where the user ID is not
an IP, I am trying to create a partial index as follows:
CREATE INDEX idx_trades_tid_part
"Christian Rengstl" <[EMAIL PROTECTED]> writes:
> I have a partial index on the expression where not allele_1=allele_2
What is that index's definition *exactly*? No handwaving please, let's
see the SQL. Also, what datatypes are these columns?
regards, tom lane
-
Christian Rengstl wrote:
Hi everyone,
i have a function that looks executes the following command in a while loop in
which it iterates through tables (aTable):
FOR rec in EXECUTE 'SELECT count(a.allele_1) as c from aTable a INNER JOIN
map_table b on(a.snp_id=upper(b.snp_id)) WHERE NOT a.allele
Hi everyone,
i have a function that looks executes the following command in a while loop in
which it iterates through tables (aTable):
FOR rec in EXECUTE 'SELECT count(a.allele_1) as c from aTable a INNER JOIN
map_table b on(a.snp_id=upper(b.snp_id)) WHERE NOT a.allele_1=a.allele_2 and
b.gene=s
Igor Shevchenko <[EMAIL PROTECTED]> writes:
> In both cases, tables are filled with ~10m of rows, "is_read" is false in the
> 1st case, and "NULL" in the 2nd. I did "VACUUM FULL ANALYSE" after both
> imports.
> Here's the problem: in the 2nd case, planner wouldn't choose an index scan
> using pa
On Mon, Dec 13, 2004 at 20:18:57 +0200,
Igor Shevchenko <[EMAIL PROTECTED]> wrote:
>
> Here's the problem: in the 2nd case, planner wouldn't choose an index scan
> using partial index on "is_read" for the following queries:
>
> explain select * from user_msg where is_read=true;
> explain selec
Hi all,
PostgreSQL v8.0.0rc1, two variants of a "user_msg" table:
create table user_msg (
message_id integer not null references message(id) on update cascade on
delete cascade,
user_id integer not null,
status smallint not null default 0,
is_read boolean not null default false,
unique
PROTECTED]
Subject: Re: [GENERAL] partial index on a text field
"Chris" <[EMAIL PROTECTED]> writes:
> chris=> create index blah on ff_index(substring(icontent, 0, 200));
> ERROR: syntax error at or near "(" at character 40
> I'm running v7.4.5.
Put an
"Chris" <[EMAIL PROTECTED]> writes:
> chris=> create index blah on ff_index(substring(icontent, 0, 200));
> ERROR: syntax error at or near "(" at character 40
> I'm running v7.4.5.
Put an extra set of parens around it:
create index blah on ff_index((substring(icontent, 0, 200)));
"subs
Hi all,
I have a text field which I'll be doing LIKE searches against so I
wanted to set up an index on it.
The data itself is too long to create a full index, so I can't just:
chris=> create index blah on ff_index(icontent);
ERROR: index row requires 21216 bytes, maximum size is 8191
So I tho
>Can anybody confirm whether these databases support partial
>indexes (and
>what are their term and syntax)?
>
>SQL Server 2000: I've glanced the T-SQL Reference and it seems it
>doesn't support it, though it supports indexing views. CLUSTERED index
>is not the same thing, right?
No. A CLUSTER
Anton Nikiforov said:
> Dear All,
> I have a question about using partial indexes.
> Lets say i have a table containing data types (table_datatype) and the
> table containing data entrys (table_data).
> While inserting into data entrys i have to number the entrys according
> to it's type, so i alwa
Paul Thomas пишет:
On 29/04/2004 09:53 Anton Nikiforov wrote:
looks like after a few tests that i've done i'm confused more than
before.
I did create a table and inserted 20 random records of two
different types of data into it.
Did you analyze the table afterwards?
Hello and thanks for the
Can anybody confirm whether these databases support partial indexes (and
what are their term and syntax)?
SQL Server 2000: I've glanced the T-SQL Reference and it seems it
doesn't support it, though it supports indexing views. CLUSTERED index
is not the same thing, right?
Oracle: I've glanced
33 matches
Mail list logo
