pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexing question
15+ messages / 7 participants
[nested] [flat]
* Indexing question
@ 2003-08-29 06:52 Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:05 ` Re: Indexing question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-30 01:07 ` Re: Indexing question Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 3 replies; 15+ messages in thread
From: Alexander Priem @ 2003-08-29 06:52 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have some tables (which can get pretty large) in which I want to record 'current' data as well as 'historical' data. This table has fields 'deleted' and 'deleteddate' (among other fields, of course). The field 'deleted' is false be default. Every record that I want to delete gets the value true for 'deleted' and 'deleteddate' is set to the date of deletion.
Since these tables are used a lot by queries that only use 'current' data, I have created a view with a where clause 'Where not deleted'. Also, I have indexed field 'deleted'.
I did this this because I read somewhere that fields that can contain NULL values will NOT be indexed.
Is this true?
Or could I ditch the 'deleted' field and just set 'deleteddate' to NULL by default and to a DATE in the case of a deleted record? I could then index the field 'deleteddate' and create a view with where clause 'Where deleteddate is null'.
Would this give the same performance as my current solution (with an indexed boolean field 'deleted') ?
I cannot test this myself at the moment as I am still in a design phase and do not have a real server available yet...
Thanks in advance,
Alexander Priem
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-29 07:03 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2 siblings, 1 reply; 15+ messages in thread
From: Tomasz Myrta @ 2003-08-29 07:03 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; +Cc: pgsql-performance
> Hi all,
>
> I have some tables (which can get pretty large) in which I want to
> record 'current' data as well as 'historical' data. This table has
> fields 'deleted' and 'deleteddate' (among other fields, of course). The
> field 'deleted' is false be default. Every record that I want to delete
> gets the value true for 'deleted' and 'deleteddate' is set to the date
> of deletion.
>
> Since these tables are used a lot by queries that only use 'current'
> data, I have created a view with a where clause 'Where not deleted'.
> Also, I have indexed field 'deleted'.
<cut>
I think the best choice for your case is using conditional indexes. It
should be much better than indexing 'deleted' field. I don't know on
which exactly fields you have to create this index - you have to check
it by yourself - what do you have in "where" clause?
Example:
create index some_index on your_table(id_field) where not deleted;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
@ 2003-08-29 07:49 ` Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:14 ` Re: Indexing question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 15+ messages in thread
From: Alexander Priem @ 2003-08-29 07:49 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-performance
So if I understand correctly I could ditch the 'deleted' field entirely and
use just the 'deleteddate' field. This 'deleteddate' field would be NULL by
default. It would contain a date value if the record is considered
'deleted'.
The index would be 'create index a on tablename(deleteddate) where
deleteddate is null'.
I could then access 'current' records with a view like 'create view x_view
as select * from tablename where deleteddate is null'.
Is that correct? This would be the best performing solution for this kind of
thing, I think (theoretically at least)?
Kind regards,
Alexander Priem.
----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Alexander Priem" <ap@cict.nl>
Cc: <pgsql-performance@postgresql.org>
Sent: Friday, August 29, 2003 9:03 AM
Subject: Re: [PERFORM] Indexing question
> > Hi all,
> >
> > I have some tables (which can get pretty large) in which I want to
> > record 'current' data as well as 'historical' data. This table has
> > fields 'deleted' and 'deleteddate' (among other fields, of course). The
> > field 'deleted' is false be default. Every record that I want to delete
> > gets the value true for 'deleted' and 'deleteddate' is set to the date
> > of deletion.
> >
> > Since these tables are used a lot by queries that only use 'current'
> > data, I have created a view with a where clause 'Where not deleted'.
> > Also, I have indexed field 'deleted'.
>
> <cut>
> I think the best choice for your case is using conditional indexes. It
> should be much better than indexing 'deleted' field. I don't know on
> which exactly fields you have to create this index - you have to check
> it by yourself - what do you have in "where" clause?
>
> Example:
> create index some_index on your_table(id_field) where not deleted;
>
>
> Regards,
> Tomasz Myrta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-29 07:59 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:41 ` Re: Indexing question Alexander Priem <ap@cict.nl>
1 sibling, 1 reply; 15+ messages in thread
From: Tomasz Myrta @ 2003-08-29 07:59 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; +Cc: pgsql-performance
> So if I understand correctly I could ditch the 'deleted' field entirely and
> use just the 'deleteddate' field. This 'deleteddate' field would be NULL by
> default. It would contain a date value if the record is considered
> 'deleted'.
>
> The index would be 'create index a on tablename(deleteddate) where
> deleteddate is null'.
>
> I could then access 'current' records with a view like 'create view x_view
> as select * from tablename where deleteddate is null'.
>
> Is that correct? This would be the best performing solution for this kind of
> thing, I think (theoretically at least)?
>
> Kind regards,
> Alexander Priem.
Near, but not exactly. You don't need field deleted - it's true.
Your example:
create index a on tablename(deleteddate) where deleteddate is null
we can translate to:
create index a on tablename(NULL) where deleteddate is null
which doesn't make too much sense.
Check your queries. You probably have something like this:
select * from tablename where not deleted and xxx
Create your index to match xxx clause - if xxx is "some_id=13", then
create your index as:
create index on tablename(some_id) where deleteddate is null;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
@ 2003-08-29 08:41 ` Alexander Priem <ap@cict.nl>
2003-08-29 08:57 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 15+ messages in thread
From: Alexander Priem @ 2003-08-29 08:41 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-performance
I think I understand what you mean :)
Let's see if that's true :
The entire table WAS like this: (just one example table, I have many more)
create table orderadvice (
orad_id serial primary key,
orad_name varchar(25) unique not null,
orad_description varchar(50) default null,
orad_value integer not null default 0,
orad_value_quan integer references quantity (quan_id) not null default 0,
orad_deleted boolean not null default false,
orad_deleteddate date default null,
orad_deletedby integer references systemuser (user_id) default null )
without oids;
Indexes were like this:
create index orad_deleted_index on orderadvice (orad_deleted);
(orad_id and orad_name indexed implicitly in the create table statement)
A view on this table:
create view orderadvice_edit as select
orad_id,orad_name,orad_description,orad_value,orad_value_quan from
orderadvice where not orad_deleted;
Most queries on this view would be like 'select * from orderadvice_edit
where orad_id=100' or 'select * from orderadvice_edit order by orad_name'.
How about the following script. Would it be better, given the type of
queries that would take place on this table?
create table orderadvice (
orad_id serial primary key,
orad_name varchar(25) not null,
orad_description varchar(50) default null,
orad_value integer not null default 0,
orad_value_quan integer references quantity (quan_id) not null default 0,
orad_deleteddate date default null,
orad_deletedby integer references systemuser (user_id) default null )
without oids;
create index orad_id_index on orderadvice (orad_id) where orad_deleteddate
is null;
create index orad_name_index on orderadvice (orad_name) where
orad_deleteddate is null;
create view orderadvice_edit as select
orad_id,orad_name,orad_description,orad_value,orad_value_quan from
orderadvice where orad_deleteddate is null;
Would queries like 'select * from orderadvice_edit where orad_id=100' or
'select * from orderadvice_edit order by orad_name' both use one of these
two partial indexes, given enough records are present in the table?
There would be a double index on the primary key this way, right?
Thanks for your advice so far,
Alexander Priem.
----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Alexander Priem" <ap@cict.nl>
Cc: <pgsql-performance@postgresql.org>
Sent: Friday, August 29, 2003 9:59 AM
Subject: Re: [PERFORM] Indexing question
> > So if I understand correctly I could ditch the 'deleted' field entirely
and
> > use just the 'deleteddate' field. This 'deleteddate' field would be NULL
by
> > default. It would contain a date value if the record is considered
> > 'deleted'.
> >
> > The index would be 'create index a on tablename(deleteddate) where
> > deleteddate is null'.
> >
> > I could then access 'current' records with a view like 'create view
x_view
> > as select * from tablename where deleteddate is null'.
> >
> > Is that correct? This would be the best performing solution for this
kind of
> > thing, I think (theoretically at least)?
> >
> > Kind regards,
> > Alexander Priem.
>
> Near, but not exactly. You don't need field deleted - it's true.
>
> Your example:
> create index a on tablename(deleteddate) where deleteddate is null
> we can translate to:
> create index a on tablename(NULL) where deleteddate is null
> which doesn't make too much sense.
>
> Check your queries. You probably have something like this:
> select * from tablename where not deleted and xxx
>
> Create your index to match xxx clause - if xxx is "some_id=13", then
> create your index as:
> create index on tablename(some_id) where deleteddate is null;
>
> Regards,
> Tomasz Myrta
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:41 ` Re: Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-29 08:57 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-29 09:08 ` Re: Indexing question Alexander Priem <ap@cict.nl>
0 siblings, 1 reply; 15+ messages in thread
From: Tomasz Myrta @ 2003-08-29 08:57 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; +Cc: pgsql-performance
> create index orad_id_index on orderadvice (orad_id) where orad_deleteddate
> is null;
> create index orad_name_index on orderadvice (orad_name) where
> orad_deleteddate is null;
>
> create view orderadvice_edit as select
> orad_id,orad_name,orad_description,orad_value,orad_value_quan from
> orderadvice where orad_deleteddate is null;
>
> Would queries like 'select * from orderadvice_edit where orad_id=100' or
> 'select * from orderadvice_edit order by orad_name' both use one of these
> two partial indexes, given enough records are present in the table?
>
> There would be a double index on the primary key this way, right?
It looks much better now. I'm not sure about the second index. Probably
it will be useless, because you sort ALL records with deleteddtata is
null. Maybe the first index will be enough.
I'm not sure what to do with doubled index on a primary key field.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:41 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 08:57 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
@ 2003-08-29 09:08 ` Alexander Priem <ap@cict.nl>
2003-08-29 14:00 ` Re: Indexing question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Alexander Priem @ 2003-08-29 09:08 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-performance
The first index is for sorting on orad_id, the second one for sorting on
orad_name. The first one would be useful for queries like 'select * from
orderadvice_edit where orad_id=100', the second one for queries like 'select
* from orderadvice_edit order by orad_name'. Right?
Does anyone know whether it is bad practise to have two indexes on the
primary key of a table? (one 'primary key' index and one partial index)
----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Alexander Priem" <ap@cict.nl>
Cc: <pgsql-performance@postgresql.org>
Sent: Friday, August 29, 2003 10:57 AM
Subject: Re: [PERFORM] Indexing question
> > create index orad_id_index on orderadvice (orad_id) where
orad_deleteddate
> > is null;
> > create index orad_name_index on orderadvice (orad_name) where
> > orad_deleteddate is null;
> >
> > create view orderadvice_edit as select
> > orad_id,orad_name,orad_description,orad_value,orad_value_quan from
> > orderadvice where orad_deleteddate is null;
> >
> > Would queries like 'select * from orderadvice_edit where orad_id=100' or
> > 'select * from orderadvice_edit order by orad_name' both use one of
these
> > two partial indexes, given enough records are present in the table?
> >
> > There would be a double index on the primary key this way, right?
>
> It looks much better now. I'm not sure about the second index. Probably
> it will be useless, because you sort ALL records with deleteddtata is
> null. Maybe the first index will be enough.
>
> I'm not sure what to do with doubled index on a primary key field.
>
> Regards,
> Tomasz Myrta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:41 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 08:57 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 09:08 ` Re: Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-29 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-29 15:13 ` Re: Indexing question Alexander Priem <ap@cict.nl>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2003-08-29 14:00 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-performance
"Alexander Priem" <ap@cict.nl> writes:
> Does anyone know whether it is bad practise to have two indexes on the
> primary key of a table? (one 'primary key' index and one partial index)
It's a little unusual, but if you get enough performance boost from it
to justify the maintenance cost of the extra index, then I can't see
anything wrong with it.
The "if" is worth checking though. I missed the start of this thread,
but what percentage of your rows do you expect to have null deleteddate?
Unless it's a pretty small percentage, I'm unconvinced that the extra
indexes will be worth their cost.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:41 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 08:57 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 09:08 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 14:00 ` Re: Indexing question Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-29 15:13 ` Alexander Priem <ap@cict.nl>
2003-08-29 16:58 ` Re: Indexing question Richard Ellis <rellis9@yahoo.com>
0 siblings, 1 reply; 15+ messages in thread
From: Alexander Priem @ 2003-08-29 15:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Well, the intention is to hold every record that ever existed in the table.
Therefore, records do not get deleted, but they get a date in the
deleteddate field. This way, we can track what changes were made to the
table(s).
So if a record gets 'deleted', the field 'deleted' is set to today's date.
If a record gets 'updated', a new record is made containing the new data,
and the old record is marked as 'deleted'.
So the percentage of 'deleted' records will grow with time, if you
understand what I mean.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Alexander Priem" <ap@cict.nl>
Cc: "Tomasz Myrta" <jasiek@klaster.net>; <pgsql-performance@postgresql.org>
Sent: Friday, August 29, 2003 4:00 PM
Subject: Re: [PERFORM] Indexing question
> "Alexander Priem" <ap@cict.nl> writes:
> > Does anyone know whether it is bad practise to have two indexes on the
> > primary key of a table? (one 'primary key' index and one partial index)
>
> It's a little unusual, but if you get enough performance boost from it
> to justify the maintenance cost of the extra index, then I can't see
> anything wrong with it.
>
> The "if" is worth checking though. I missed the start of this thread,
> but what percentage of your rows do you expect to have null deleteddate?
> Unless it's a pretty small percentage, I'm unconvinced that the extra
> indexes will be worth their cost.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:41 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 08:57 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 09:08 ` Re: Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 14:00 ` Re: Indexing question Tom Lane <tgl@sss.pgh.pa.us>
2003-08-29 15:13 ` Re: Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-29 16:58 ` Richard Ellis <rellis9@yahoo.com>
0 siblings, 0 replies; 15+ messages in thread
From: Richard Ellis @ 2003-08-29 16:58 UTC (permalink / raw)
To: pgsql-performance
On Fri, Aug 29, 2003 at 05:13:52PM +0200, Alexander Priem wrote:
> Well, the intention is to hold every record that ever existed in the table.
> Therefore, records do not get deleted, but they get a date in the
> deleteddate field. This way, we can track what changes were made to the
> table(s).
>
> So if a record gets 'deleted', the field 'deleted' is set to today's date.
> If a record gets 'updated', a new record is made containing the new data,
> and the old record is marked as 'deleted'.
>
> So the percentage of 'deleted' records will grow with time, if you
> understand what I mean.
Did you consider a two table implimentation. 1 table "live_table"
containing the non-deleted records, a second table "deleted_table"
containing the deleted records, along with the "deleted_date" field. Keep
the two in sync column type/number wise, and use a before delete trigger
function on "live_table" to actually insert a copy of the deleted row plus
"deleted_date" into "deleted_table" before performing the delete on
"live_table".
You could also use a before update trigger to keep old copies of updated
records in the same way.
Then you would only incur the performance loss of scanning/etc. the deleted
records when you actually need to pull up deleted plus live records.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Re: Indexing question Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Re: Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-29 08:14 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 15+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-29 08:14 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-performance
> So if I understand correctly I could ditch the 'deleted' field entirely
and
> use just the 'deleteddate' field. This 'deleteddate' field would be NULL
by
> default. It would contain a date value if the record is considered
> 'deleted'.
>
> The index would be 'create index a on tablename(deleteddate) where
> deleteddate is null'.
>
> I could then access 'current' records with a view like 'create view x_view
> as select * from tablename where deleteddate is null'.
>
> Is that correct? This would be the best performing solution for this kind
of
> thing, I think (theoretically at least)?
Yes, I think it would be best. Definitely better than your current
solution.
Cheers,
Chris
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-29 07:05 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 15+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-29 07:05 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; pgsql-performance
Remember to consider partial indexes:
eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT NULL
Chris
----- Original Message -----
From: Alexander Priem
To: pgsql-performance@postgresql.org
Sent: Friday, August 29, 2003 2:52 PM
Subject: [PERFORM] Indexing question
Hi all,
I have some tables (which can get pretty large) in which I want to record 'current' data as well as 'historical' data. This table has fields 'deleted' and 'deleteddate' (among other fields, of course). The field 'deleted' is false be default. Every record that I want to delete gets the value true for 'deleted' and 'deleteddate' is set to the date of deletion.
Since these tables are used a lot by queries that only use 'current' data, I have created a view with a where clause 'Where not deleted'. Also, I have indexed field 'deleted'.
I did this this because I read somewhere that fields that can contain NULL values will NOT be indexed.
Is this true?
Or could I ditch the 'deleted' field and just set 'deleteddate' to NULL by default and to a DATE in the case of a deleted record? I could then index the field 'deleteddate' and create a view with where clause 'Where deleteddate is null'.
Would this give the same performance as my current solution (with an indexed boolean field 'deleted') ?
I cannot test this myself at the moment as I am still in a design phase and do not have a real server available yet...
Thanks in advance,
Alexander Priem
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
@ 2003-08-30 01:07 ` Rudi Starcevic <rudi@oasis.net.au>
2 siblings, 0 replies; 15+ messages in thread
From: Rudi Starcevic @ 2003-08-30 01:07 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; pgsql-performance
Hi,
> I have some tables (which can get pretty large) in which I want to
> record 'current' data as well as 'historical' data.
Another solution can be using a trigger and function to record every
transaction to a 'logging' table.
This way you'll have one 'current' table and one 'historical' table
The 'historical' table will contain every transaction recorded from
the current table.
Regards
Rudi.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Indexing question
@ 2004-06-15 21:29 Stan Bielski <bielski@ece.cmu.edu>
2004-06-20 18:45 ` Re: Indexing question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Stan Bielski @ 2004-06-15 21:29 UTC (permalink / raw)
To: pgsql-performance
Hello all,
I have a rather large table (~20 GB) of network logs taken over the period
of a month and stored under postgres 7.3. I'm trying to create an indexing
scheme so that I can do a query with a "where time > foo and time < bar"
and get the results without too much waiting. With this in mind, I created
a new database ordering each log entry by the time values (seconds, and
microseconds), and then created an index on the time value for seconds. I
didn't use the clusterdb command, but I did do a "select * into foo_tbl
order by time", which I understand to be the functional equivalent. The
result looks something like this:
Table "public.allflow_tv_mydoom"
Column | Type | Modifiers
------------+---------+-----------
tv_s | bigint |
tv_us | bigint |
src | inet |
dst | inet |
sport | integer |
dport | integer |
bytes_in | bigint |
bytes_out | bigint |
isn_in | bigint |
isn_out | bigint |
num_starts | integer |
result | integer |
flags | integer |
age_s | bigint |
age_us | bigint |
ttl_left | bigint |
end_s | bigint |
end_us | bigint |
Indexes: allflow_tv_mydoom_x btree (tv_s)
with tv_s, of course, being my value in seconds. I followed this up with
an ANALYZE so postrgres could absorb this structure into its logic.
However, whenever I want to do my query, it *insists* on doing a
sequential scan, even when I explicitly turn sequential scan off in
postgres.conf... here's an example:
standb=# explain select * from allflow_tv_mydoom where tv_s < 1074200099
and tv_s > 107506499;
QUERY PLAN
-----------------------------------------------------------------------------------
Seq Scan on allflow_tv_mydoom (cost=100000000.00..102303307.94 rows=1
width=132)
Filter: ((tv_s < 1074200099) AND (tv_s > 107506499))
(2 rows)
In this query I'm basically asking for a day's worth of data. It should be
straightforward: it's all laid out in order and indexed, but it still
insists on (what I believe to be) tearing through the entire DB and
filtering out the other time values as it sees them. Even if I want just
the data from a particular second, it does the same thing:
standb=# explain select * from allflow_tv_mydoom where tv_s = 1074200099;
QUERY PLAN
-------------------------------------------------------------------------------------
Seq Scan on allflow_tv_mydoom (cost=100000000.00..102140682.45 rows=145
width=132)
Filter: (tv_s = 1074200099)
(2 rows)
Naturally, this is incredibly frustrating because it takes forever,
regardless of the query.
The funny thing though is that I have the same data in another table
where it is ordered and indexed by IP address, and the queries use the
index and work the way I want them to. Example:
standb=# explain select * from flow_ip_mydoom where src = '10.0.5.5';
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using flow_ip_mydoom_x on flow_ip_mydoom (cost=0.00..333.41
rows=376 width=132)
Index Cond: (src = '10.0.5.5'::inet)
(2 rows)
Can someone please explain to me what's going on and how to fix it? If
there's an easier way to 'jump' to different time regions in the table
without indexing all the different seconds values, I'd like to know this
as well.
Thanks a bunch,
-S
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Indexing question
2004-06-15 21:29 Indexing question Stan Bielski <bielski@ece.cmu.edu>
@ 2004-06-20 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2004-06-20 18:45 UTC (permalink / raw)
To: Stan Bielski <bielski@ece.cmu.edu>; +Cc: pgsql-performance
Stan Bielski <bielski@ece.cmu.edu> writes:
> Table "public.allflow_tv_mydoom"
> Column | Type | Modifiers
> ------------+---------+-----------
> tv_s | bigint |
^^^^^^
> Indexes: allflow_tv_mydoom_x btree (tv_s)
> standb=# explain select * from allflow_tv_mydoom where tv_s < 1074200099
> and tv_s > 107506499;
> [ gives seqscan ]
This is a FAQ :-(. Unadorned integer constants are taken to be int4
not int8 (unless they are too large for int4), and cross-data-type
comparisons are not indexable in existing releases. So you have to
explicitly cast the comparison values to int8:
explain select * from allflow_tv_mydoom where tv_s < 1074200099::bigint
and tv_s > 107506499::bigint;
(or use the standard CAST syntax if you prefer).
7.5 will have a fix for this ancient annoyance.
BTW, is there a reason to be using tv_s+tv_us and not just a single
timestamptz column?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2004-06-20 18:45 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-29 06:52 Indexing question Alexander Priem <ap@cict.nl>
2003-08-29 07:03 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-29 07:49 ` Alexander Priem <ap@cict.nl>
2003-08-29 07:59 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-29 08:41 ` Alexander Priem <ap@cict.nl>
2003-08-29 08:57 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-29 09:08 ` Alexander Priem <ap@cict.nl>
2003-08-29 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-29 15:13 ` Alexander Priem <ap@cict.nl>
2003-08-29 16:58 ` Richard Ellis <rellis9@yahoo.com>
2003-08-29 08:14 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-29 07:05 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-30 01:07 ` Rudi Starcevic <rudi@oasis.net.au>
2004-06-15 21:29 Indexing question Stan Bielski <bielski@ece.cmu.edu>
2004-06-20 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
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