Re: [GENERAL] Failure to use indexes (fwd)
Greetings! Table definitions were included in the original post. I can repost (or send directly) if desired. I am using to implement database partitioning based on the day. Postgresql 8.0.1 (and previous) has a number of issues when it comes to very large tables. Currently we have anywhere from 4,000,000 to 10,000,000 rows per day to deal with. With a running, we can't seem to cancel it until it finishes (which takes a bit of time when you have over 3 months of data). Insert/search performance also degrades with one large table (this gets into the issues of dealing with large volumes of time series data - really wish Postgresql would have the concept of a queue table where rows are always inserted at the end, and deletes only happen at the beginning, with block allocation). By using , we can truncate a days worth of data very quickly, and only vacuum changing tables. Hopefully, the use of constraints in the query optimizer will make it into 8.1, so it will help some of our queries. I could hand optimize queries, but then I am essentially implementing an optimizer in our application code, which is definitely the wrong place to put it (also, if I was to go the full way, then I might switch to a database that supports time series data better, but would lose the ad-hoc query abilities of SQL). Indexes are on all the children (as per the post). in addition, when I use child tables directly, the indexes are used in the queries (which is one of the reasons why I suspect a bug related to pushing optimization information through ). Note that I also posted a followup that showed how a 1 row set would not use indexes when going through whereas a simple = would. Regards! Ed On Wed, 3 Aug 2005, Dr NoName wrote: > post your table definitions. I suspect you are > indexing the parent table but not the children. > > btw, we tried using inherited tables in our > application and quickly found out that they are more > trouble then they are worth (at least the way they are > implemented in postgresql). There are other, more > portable ways of mapping a class hierarchy to > table(s). A few techniques are described in Fowler's > Patterns of Enterprise Application Architecture. > > hope this helps, > > Eugene > > > --- Edmund Dengler <[EMAIL PROTECTED]> wrote: > > > Greetings! > > > > I have already increased the stats from 10 to 100. > > In addition, if I > > specify individual tables, then the indexes are > > used. However, when I go > > through the , then indexes are not used. I > > will try and expand > > the statistics, but suspect it is not the root cause > > of the problem. > > > > Regards! > > Ed > > > > > > On Tue, 2 Aug 2005, Scott Marlowe wrote: > > > > > On Tue, 2005-08-02 at 16:06, Dr NoName wrote: > > > > The solution to my problem was to increase > > statistics > > > > value and do another analyze. You can also > > change > > > > default_statistics_target parameter in > > > > postgresql.conf. Don't know if that's related to > > the > > > > problem you're seeing, but it's worth a try. > > > > > > Cool postgresql trick: > > > > > > alter database test set > > default_statistics_target=200; > > > > > > You can change the default for a databases's new > > tables too. > > > > > > ---(end of > > broadcast)--- > > > TIP 2: Don't 'kill -9' the postmaster > > > > > > > ---(end of > > broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Failure to use indexes (fwd)
Greetings! I have already increased the stats from 10 to 100. In addition, if I specify individual tables, then the indexes are used. However, when I go through the , then indexes are not used. I will try and expand the statistics, but suspect it is not the root cause of the problem. Regards! Ed On Tue, 2 Aug 2005, Scott Marlowe wrote: > On Tue, 2005-08-02 at 16:06, Dr NoName wrote: > > The solution to my problem was to increase statistics > > value and do another analyze. You can also change > > default_statistics_target parameter in > > postgresql.conf. Don't know if that's related to the > > problem you're seeing, but it's worth a try. > > Cool postgresql trick: > > alter database test set default_statistics_target=200; > > You can change the default for a databases's new tables too. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Failure to use indexes (fwd)
Greetings all!
Given the quiet, I assume that there is no experience with index issues on
inherited tables? Just seeing if anybody may have any ideas or suggested
work arounds (I seem to have found one by constructing a query that does
all the joins between inherited tables explicitely - this causes the
indexes to be used - still trying to make sure it is a legitimate method).
Regards!
Ed
-- Forwarded message --
Date: Fri, 29 Jul 2005 14:23:29 -0400 (EDT)
From: Edmund Dengler <[EMAIL PROTECTED]>
To: Postgresql-General
Subject: Re: [GENERAL] Failure to use indexes
Greetings!
I managed to get an even simpler example:
select *
from eventlog.record_main
where luid in (
select luid from eventlog_partition._20050706__raw_record
order by luid limit 1
)
;
If I use <=> rather than , postgresql uses index scanning. As soon as
I use (ie, a set to compare against), I get sequential scanning,
event though the set size is only a single element.
Regards!
Ed
On Fri, 29 Jul 2005, Edmund Dengler wrote:
> Greetings!
>
> I am using to partition several tables. When I perform a query
> on another table, and then try to join against an inherited table set, the
> optimizer does not use any indexes to perform the join.
>
> This is 8.0.1. Is this fixed in 8.0.2 or 8.0.3?
>
> The query:
>
> explain
> select *
> from (
> select * from eventlog.record_classification as record_classification
> where
> time_written >= '2005-07-06 00:00:00+00'::timestamptz
> and time_written < '2005-07-06 00:00:00+00'::timestamptz
> order by time_written, luid
> offset 0
> limit 500
> ) as classification
> join eventlog.record_main as main using (luid, time_written)
> ;
>
> The explanation:
>
>
> QUERY PLAN
> ---
> Hash Join (cost=51.15..20191003.89 rows=208027 width=178)
>Hash Cond: (("outer".luid = "inner".luid) AND ("outer".time_written =
> "inner".time_written))
>-> Append (cost=0.00..14641488.64 rows=554738383 width=96)
> -> Seq Scan on record_main main (cost=0.00..0.00 rows=1 width=96)
> -> Seq Scan on _20050723__record_main main (cost=0.00..94078.62
> rows=3564462 width=96)
> -> Seq Scan on _20050724__record_main main (cost=0.00..110075.12
> rows=4170512 width=96)
> -> Seq Scan on _20050725__record_main main (cost=0.00..122836.02
> rows=4654002 width=96)
> -> Seq Scan on _20050726__record_main main (cost=0.00..142347.71
> rows=5393271 width=96)
> -> Seq Scan on _20050727__record_main main (cost=0.00..130858.80
> rows=4957980 width=96)
>
> (and so on, currently 123 such inheritd tables)
>
>-> Hash (cost=51.07..51.07 rows=15 width=98)
> -> Subquery Scan classification (cost=50.89..51.07 rows=15
> width=98)
>-> Limit (cost=50.89..50.92 rows=15 width=98)
> -> Sort (cost=50.89..50.92 rows=15 width=98)
>Sort Key: record_classification.time_written,
> record_classification.luid
>-> Result (cost=0.00..50.59 rows=15 width=98)
> -> Append (cost=0.00..50.59 rows=15
> width=98)
>-> Seq Scan on record_classification
> (cost=0.00..0.00 rows=1 width=98)
> Filter: ((time_written >=
> '2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
> '2005-07-06 00:00:00+00'::timestamp with time zone))
>-> Index Scan using
> _20050705__record_classification_time_written_idx on
> _20050705__record_classification record_classification (cost=0.00..3.46
> rows=1 width=54)
> Index Cond: ((time_written >=
> '2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
> '2005-07-06 00:00:00+00'::timestamp with time zone))
>-> Index Scan using
> _20050701__record_classification_time_written_idx on
> _20050701__record_classification record_classification (cost=0.00..3.59
> rows=1 width=54)
> Index Cond: ((time_written >=
> '2005-07-06 00:00:0
Re: [GENERAL] Failure to use indexes
Greetings!
I managed to get an even simpler example:
select *
from eventlog.record_main
where luid in (
select luid from eventlog_partition._20050706__raw_record
order by luid limit 1
)
;
If I use <=> rather than , postgresql uses index scanning. As soon as
I use (ie, a set to compare against), I get sequential scanning,
event though the set size is only a single element.
Regards!
Ed
On Fri, 29 Jul 2005, Edmund Dengler wrote:
> Greetings!
>
> I am using to partition several tables. When I perform a query
> on another table, and then try to join against an inherited table set, the
> optimizer does not use any indexes to perform the join.
>
> This is 8.0.1. Is this fixed in 8.0.2 or 8.0.3?
>
> The query:
>
> explain
> select *
> from (
> select * from eventlog.record_classification as record_classification
> where
> time_written >= '2005-07-06 00:00:00+00'::timestamptz
> and time_written < '2005-07-06 00:00:00+00'::timestamptz
> order by time_written, luid
> offset 0
> limit 500
> ) as classification
> join eventlog.record_main as main using (luid, time_written)
> ;
>
> The explanation:
>
>
> QUERY PLAN
> ---
> Hash Join (cost=51.15..20191003.89 rows=208027 width=178)
>Hash Cond: (("outer".luid = "inner".luid) AND ("outer".time_written =
> "inner".time_written))
>-> Append (cost=0.00..14641488.64 rows=554738383 width=96)
> -> Seq Scan on record_main main (cost=0.00..0.00 rows=1 width=96)
> -> Seq Scan on _20050723__record_main main (cost=0.00..94078.62
> rows=3564462 width=96)
> -> Seq Scan on _20050724__record_main main (cost=0.00..110075.12
> rows=4170512 width=96)
> -> Seq Scan on _20050725__record_main main (cost=0.00..122836.02
> rows=4654002 width=96)
> -> Seq Scan on _20050726__record_main main (cost=0.00..142347.71
> rows=5393271 width=96)
> -> Seq Scan on _20050727__record_main main (cost=0.00..130858.80
> rows=4957980 width=96)
>
> (and so on, currently 123 such inheritd tables)
>
>-> Hash (cost=51.07..51.07 rows=15 width=98)
> -> Subquery Scan classification (cost=50.89..51.07 rows=15
> width=98)
>-> Limit (cost=50.89..50.92 rows=15 width=98)
> -> Sort (cost=50.89..50.92 rows=15 width=98)
>Sort Key: record_classification.time_written,
> record_classification.luid
>-> Result (cost=0.00..50.59 rows=15 width=98)
> -> Append (cost=0.00..50.59 rows=15
> width=98)
>-> Seq Scan on record_classification
> (cost=0.00..0.00 rows=1 width=98)
> Filter: ((time_written >=
> '2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
> '2005-07-06 00:00:00+00'::timestamp with time zone))
>-> Index Scan using
> _20050705__record_classification_time_written_idx on
> _20050705__record_classification record_classification (cost=0.00..3.46
> rows=1 width=54)
> Index Cond: ((time_written >=
> '2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
> '2005-07-06 00:00:00+00'::timestamp with time zone))
>-> Index Scan using
> _20050701__record_classification_time_written_idx on
> _20050701__record_classification record_classification (cost=0.00..3.59
> rows=1 width=54)
> Index Cond: ((time_written >=
> '2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
> '2005-07-06 00:00:00+00'::timestamp with time zone))
>-> Index Scan using
> _20050702__record_classification_time_written_idx on
> _20050702__record_classification record_classification (cost=0.00..3.69
> rows=1 width=54)
> Index Cond: ((time_written >=
> '2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
> '
[GENERAL] Failure to use indexes
Greetings!
I am using to partition several tables. When I perform a query
on another table, and then try to join against an inherited table set, the
optimizer does not use any indexes to perform the join.
This is 8.0.1. Is this fixed in 8.0.2 or 8.0.3?
The query:
explain
select *
from (
select * from eventlog.record_classification as record_classification
where
time_written >= '2005-07-06 00:00:00+00'::timestamptz
and time_written < '2005-07-06 00:00:00+00'::timestamptz
order by time_written, luid
offset 0
limit 500
) as classification
join eventlog.record_main as main using (luid, time_written)
;
The explanation:
QUERY PLAN
---
Hash Join (cost=51.15..20191003.89 rows=208027 width=178)
Hash Cond: (("outer".luid = "inner".luid) AND ("outer".time_written =
"inner".time_written))
-> Append (cost=0.00..14641488.64 rows=554738383 width=96)
-> Seq Scan on record_main main (cost=0.00..0.00 rows=1 width=96)
-> Seq Scan on _20050723__record_main main (cost=0.00..94078.62
rows=3564462 width=96)
-> Seq Scan on _20050724__record_main main (cost=0.00..110075.12
rows=4170512 width=96)
-> Seq Scan on _20050725__record_main main (cost=0.00..122836.02
rows=4654002 width=96)
-> Seq Scan on _20050726__record_main main (cost=0.00..142347.71
rows=5393271 width=96)
-> Seq Scan on _20050727__record_main main (cost=0.00..130858.80
rows=4957980 width=96)
(and so on, currently 123 such inheritd tables)
-> Hash (cost=51.07..51.07 rows=15 width=98)
-> Subquery Scan classification (cost=50.89..51.07 rows=15 width=98)
-> Limit (cost=50.89..50.92 rows=15 width=98)
-> Sort (cost=50.89..50.92 rows=15 width=98)
Sort Key: record_classification.time_written,
record_classification.luid
-> Result (cost=0.00..50.59 rows=15 width=98)
-> Append (cost=0.00..50.59 rows=15 width=98)
-> Seq Scan on record_classification
(cost=0.00..0.00 rows=1 width=98)
Filter: ((time_written >=
'2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
'2005-07-06 00:00:00+00'::timestamp with time zone))
-> Index Scan using
_20050705__record_classification_time_written_idx on
_20050705__record_classification record_classification (cost=0.00..3.46 rows=1
width=54)
Index Cond: ((time_written >=
'2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
'2005-07-06 00:00:00+00'::timestamp with time zone))
-> Index Scan using
_20050701__record_classification_time_written_idx on
_20050701__record_classification record_classification (cost=0.00..3.59 rows=1
width=54)
Index Cond: ((time_written >=
'2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
'2005-07-06 00:00:00+00'::timestamp with time zone))
-> Index Scan using
_20050702__record_classification_time_written_idx on
_20050702__record_classification record_classification (cost=0.00..3.69 rows=1
width=54)
Index Cond: ((time_written >=
'2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
'2005-07-06 00:00:00+00'::timestamp with time zone))
-> Index Scan using
_20050703__record_classification_time_written_idx on
_20050703__record_classification record_classification (cost=0.00..3.70 rows=1
width=54)
Index Cond: ((time_written >=
'2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
'2005-07-06 00:00:00+00'::timestamp with time zone))
(and so on)
-> Index Scan using
_20050714__record_classification_time_written_idx on
_20050714__record_classification record_classification (cost=0.00..3.69 rows=1
width=53)
Index Cond: ((time_written >=
'2005-07-06 00:00:00+00'::timestamp with time zone) AND (time_written <
'2005-07-06 00:00:00+00'::timestamp with time zone))
(164 rows)
Sample tables:
eventlog=# \d eventlog_partition._20050723__record_main
Table "eventlog_partition._20050723__re
Re: [GENERAL] Deletes and large tables
Just did a sanity check. I dumped the DB schema, and there is indeed a foreign key reference into the table. Now interestingly, the table pointing in has no index on the column, but is a relatively small table with only entries near the end of the large table. So looks like I was getting CPU bound because of a sequental scan of the smaller table per delete. Dropped the constraint, and deletes are now much faster. Regards! Ed On Fri, 10 Jun 2005, David Gagnon wrote: > > >This table has 3 foreign keys, but that should not matter during deletes. > >In addition, the tables being referred to are small, and should be in > >cache. > > > > > I'm talking about FK that point this table... Not FK defined for this > table that point to other table. If Table A is referenced by 10 other > tables 10 referencial check are needed. > > > >There are no tables depending on it for references, so no dependent > >triggers should be running. > > > >Also, if this was a foreign key issue, I would expect I/O issues/bounds > >and not CPU. > > > > > Maybe... I'm honetly not sure. > > Like I said in my previous mail... I got a similar problem (maybe not > the same). It was taking 10 minutes to delete 10k line in a table. I > turn on some log info in postgresql.conf and I saw that for each row > deleted 4 selects were issued to check FK. I drop those FK and the > after the delete was taking less than a second. > > Hope it help > /David > > > > >Regards! > >Ed > > > > > >On Fri, 10 Jun 2005, Richard Huxton wrote: > > > > > > > >>Edmund Dengler wrote: > >> > >> > >>>Greetings! > >>> > >>>We have a table with more than 250 million rows. I am trying to delete the > >>>first 100,000 rows (based on a bigint primary key), and I had to cancel > >>>after 4 hours of the system not actually finishing the delete. I wrote a > >>>script to delete individual rows 10,000 at a time using transactions, and > >>>am finding each individual delete takes on the order of 0.1 seconds to 2-3 > >>>seconds. There are 4 indexes on the table, one of which is very "hashlike" > >>>(ie, distribution is throught the index for sequential rows). > >>> > >>> > >>I don't suppose it's off checking foreign-keys in a lot of tables is it? > >> > >>-- > >> Richard Huxton > >> Archonet Ltd > >> > >> > >> > > > >---(end of broadcast)--- > >TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to [EMAIL PROTECTED] so that your > > message can get through to the mailing list cleanly > > > > > > > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Deletes and large tables
Greetings! This table has 3 foreign keys, but that should not matter during deletes. In addition, the tables being referred to are small, and should be in cache. There are no tables depending on it for references, so no dependent triggers should be running. Also, if this was a foreign key issue, I would expect I/O issues/bounds and not CPU. Regards! Ed On Fri, 10 Jun 2005, Richard Huxton wrote: > Edmund Dengler wrote: > > Greetings! > > > > We have a table with more than 250 million rows. I am trying to delete the > > first 100,000 rows (based on a bigint primary key), and I had to cancel > > after 4 hours of the system not actually finishing the delete. I wrote a > > script to delete individual rows 10,000 at a time using transactions, and > > am finding each individual delete takes on the order of 0.1 seconds to 2-3 > > seconds. There are 4 indexes on the table, one of which is very "hashlike" > > (ie, distribution is throught the index for sequential rows). > > I don't suppose it's off checking foreign-keys in a lot of tables is it? > > -- >Richard Huxton >Archonet Ltd > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Deletes and large tables
Greetings! We have a table with more than 250 million rows. I am trying to delete the first 100,000 rows (based on a bigint primary key), and I had to cancel after 4 hours of the system not actually finishing the delete. I wrote a script to delete individual rows 10,000 at a time using transactions, and am finding each individual delete takes on the order of 0.1 seconds to 2-3 seconds. There are 4 indexes on the table, one of which is very "hashlike" (ie, distribution is throught the index for sequential rows). Is this considered "normal" for delete speed? Currently using 8.0.1. Drives are capable of 40MB/sec sustained transfers, but only acheiving about 2-10kB/sec, and mostly CPU bound. Regards! Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] INHERITS and planning
Greetings! Is there an issue when a large number of INHERITS tables exist for planning? We have 2 base tables, and use INHERITS to partition the data. When we get around 2000-2200 sub-tables (approx 1000-1100 per base table), planning a SELECT statement on the base table (ie, to search all sub-tables) will start slowing down dramatically (ie, feels like something exponential OR some kind of in-memory to on-disk transition). I haven't done enough to really plot out the planning times, but definitely around 1600 tables we were getting sub-second plans, and around 2200 we were above 30 seconds. Also, is there any plans to support proper partitioning/binning of data rather than through INHERITS? I know it has been mentioned as upcoming sometime similar to Oracle. I would like to put in a vote to support "auto-binning" in which a function is called to define the bin. The Oracle model really only supports: (1) explicit partitioning (ie, every new partition must be defined), or (2) hash binning. What we deal with is temporal data, and would like to bin on the hour or day "automatically", hopefully to support truncating whole bins. This helps us 2 ways: (1) data deletion is bulk (we currently drop a full inherited table), (2) cancelling a VACUUM/SELECT doesn't take forever while the execution engine finishes "this table" (we have had cancels take 2 hours because the VACUUM was on a very large single table). Regards! Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] vulnerability/SSL (fwd)
Greetings! Does anybody know how well the optimizer works when dealing with inherited tables? I am currently using 8.0.1. I have a table called eventlog.record_main, and a number of inherited tables to partition the data (called eventlog_partition.___record_main). is the primary key (all tables have this indexed via the primary key). The reason for doing this is that a single table would be way too big (there are on average 6-7 million rows per table) so that vacuum and deletes would be inefficient. Inserting has been much more efficient using this mechanism. When I try the following query, I get sequential scans: explain select * from eventlog.record_main order by luid limit 5; QUERY PLAN -- Limit (cost=160800332.75..160800332.77 rows=5 width=92) -> Sort (cost=160800332.75..161874465.60 rows=429653138 width=92) Sort Key: eventlog.record_main.luid -> Result (cost=0.00..11138614.37 rows=429653138 width=92) -> Append (cost=0.00..11138614.37 rows=429653138 width=92) -> Seq Scan on record_main (cost=0.00..0.00 rows=1 width=92) -> Seq Scan on _20050401__record_main record_main (cost=0.00..223029.64 rows=8620164 width=92) -> Seq Scan on _20050402__record_main record_main (cost=0.00..201536.46 rows=7789446 width=92) -> Seq Scan on _20050403__record_main record_main (cost=0.00..211277.34 rows=8165934 width=92) -> Seq Scan on _20050404__record_main record_main (cost=0.00..219381.70 rows=8479170 width=92) -> Seq Scan on _20050405__record_main record_main (cost=0.00..226305.94 rows=8746794 width=92) (and so on) Yet, when I run the query on a single table, I get index usage: explain select * from eventlog_partition._20050601__record_main order by luid limit 5; QUERY PLAN Limit (cost=0.00..0.15 rows=5 width=92) -> Index Scan using _20050601__record_main_pkey on _20050601__record_main (cost=0.00..163375.23 rows=5460021 width=92) (2 rows) This means that any query that limits the rows will run extremely inefficiently. Given a limit of 5, at most only 5 rows need to be considered in each partition sub-table, so an optimal plan would run a sub-query in each table limited to 5 rows, and then merge the results. Any ideas/fixes/patches? Regards! Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Optimizer and inherited tables
(Sorry, wrong subject line got sent) Greetings! Does anybody know how well the optimizer works when dealing with inherited tables? I am currently using 8.0.1. I have a table called eventlog.record_main, and a number of inherited tables to partition the data (called eventlog_partition.___record_main). is the primary key (all tables have this indexed via the primary key). The reason for doing this is that a single table would be way too big (there are on average 6-7 million rows per table) so that vacuum and deletes would be inefficient. Inserting has been much more efficient using this mechanism. When I try the following query, I get sequential scans: explain select * from eventlog.record_main order by luid limit 5; QUERY PLAN -- Limit (cost=160800332.75..160800332.77 rows=5 width=92) -> Sort (cost=160800332.75..161874465.60 rows=429653138 width=92) Sort Key: eventlog.record_main.luid -> Result (cost=0.00..11138614.37 rows=429653138 width=92) -> Append (cost=0.00..11138614.37 rows=429653138 width=92) -> Seq Scan on record_main (cost=0.00..0.00 rows=1 width=92) -> Seq Scan on _20050401__record_main record_main (cost=0.00..223029.64 rows=8620164 width=92) -> Seq Scan on _20050402__record_main record_main (cost=0.00..201536.46 rows=7789446 width=92) -> Seq Scan on _20050403__record_main record_main (cost=0.00..211277.34 rows=8165934 width=92) -> Seq Scan on _20050404__record_main record_main (cost=0.00..219381.70 rows=8479170 width=92) -> Seq Scan on _20050405__record_main record_main (cost=0.00..226305.94 rows=8746794 width=92) (and so on) Yet, when I run the query on a single table, I get index usage: explain select * from eventlog_partition._20050601__record_main order by luid limit 5; QUERY PLAN Limit (cost=0.00..0.15 rows=5 width=92) -> Index Scan using _20050601__record_main_pkey on _20050601__record_main (cost=0.00..163375.23 rows=5460021 width=92) (2 rows) This means that any query that limits the rows will run extremely inefficiently. Given a limit of 5, at most only 5 rows need to be considered in each partition sub-table, so an optimal plan would run a sub-query in each table limited to 5 rows, and then merge the results. Any ideas/fixes/patches? Regards! Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Multi-column indexes
Hi Tom! Yep, there are a large number of host_luid/log_luid combinations (there are approximatly 5-10 hosts and 1-3 logs per system we are running). Thanks for the recommended workaround, I'll have a try at it at some point tomorrow. Regards! Ed On Sat, 15 Jan 2005, Tom Lane wrote: > Edmund Dengler <[EMAIL PROTECTED]> writes: > > "record_to_process_idx" unique, btree (host_luid, log_luid, luid) WHERE > > (error IS NULL) > > > explain analyze > > select record > > from agent.record > > where host_luid = 3::bigint > > and log_luid = 2::bigint > > and error is null > > order by host_luid desc, log_luid desc, luid desc > > limit 1 > > > Limit (cost=0.00..1.47 rows=1 width=286) (actual > > time=249064.949..249064.950 rows=1 loops=1) > >-> Index Scan Backward using record_to_process_idx on record > > (cost=0.00..13106.73 rows=8898 width=286) (actual > > time=249064.944..249064.944 rows=1 loops=1) > > Index Cond: ((host_luid = 3::bigint) AND (log_luid = 2::bigint)) > > Filter: (error IS NULL) > > Total runtime: 249065.004 ms > > Are there a whole lotta rows with that host_luid/log_luid combination? > > What's happening is that the index search initially finds the first such > row, and then it has to step to the last such row to start the backwards > scan. This is fixed as of 8.0, but all earlier releases are going to be > slow in that scenario. It's got nothing to do with single vs multi > column indexes, it is just a shortcoming of the startup code for > backwards index scans. (I get the impression that the original > implementation of Postgres' btree indexes only supported unique indexes, > because there were a number of places where it was horridly inefficient > for large numbers of equal keys. I think this 8.0 fix is the last such > issue.) > > Since your index has an additional column, there is a hack you can use > to get decent performance in 7.4 and before. Add a dummy condition on > the last column: > where host_luid = 3::bigint > and log_luid = 2::bigint > AND LUID <= someverylargevalue::bigint > and error is null > order by host_luid desc, log_luid desc, luid desc > limit 1 > Now, instead of positioning to the first row with value (3,2,anything) > the initial btree descent will home in on the end of that range, and > so the expensive stepping over all the rows between is avoided. > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Multi-column indexes
Greetings!
I have a technical question concerning multi-column indexes and their
implementation. I tried looking for the answr in the docs but couldn't
find anything.
I have the following table:
eventlog=> \d agent.record
Table "agent.record"
Column | Type |
Modifiers
+--+-
luid | bigint | not null default
nextval('agent.record_luid_seq'::text)
host_luid | bigint |
remote_system_luid | bigint |
log_luid | bigint | not null
time_logged| timestamp with time zone | not null default now()
record | bytea| not null
error | boolean |
error_reason | text |
Indexes:
"record_pkey" primary key, btree (luid)
"record_to_process_idx" unique, btree (host_luid, log_luid, luid) WHERE
(error IS NULL)
"record_to_process_idx2" unique, btree (luid) WHERE (error IS NULL)
"record_last_logged_idx" btree (time_logged, host_luid, log_luid, luid)
Foreign-key constraints:
"$1" FOREIGN KEY (host_luid) REFERENCES eventlog.host(luid) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (remote_system_luid) REFERENCES
eventlog.remote_system(luid)
"$3" FOREIGN KEY (log_luid) REFERENCES eventlog.log(luid) ON UPDATE CASCADE
ON DELETE CASCADE
consisting of 27306578 rows.
So I try running the following query:
explain analyze
select record
from agent.record
where host_luid = 3::bigint
and log_luid = 2::bigint
and error is null
order by host_luid desc, log_luid desc, luid desc
limit 1
I get the following query plan:
Limit (cost=0.00..1.47 rows=1 width=286) (actual time=249064.949..249064.950
rows=1 loops=1)
-> Index Scan Backward using record_to_process_idx on record
(cost=0.00..13106.73 rows=8898 width=286) (actual time=249064.944..249064.944
rows=1 loops=1)
Index Cond: ((host_luid = 3::bigint) AND (log_luid = 2::bigint))
Filter: (error IS NULL)
Total runtime: 249065.004 ms
(5 rows)
Now, this plan seems kinda slow, in the sense of scanning backwards. And
it takes quite a long time (compared to seeking the last row based only on
, for example). It feels that if I have values of
(1,2,3,4,5), that the above is scanning through _all_ 5 entries, then 4
entries, and then finally gets to 3.
So, now to my question: is this really happening?
I guess it breaks down to how these indexes are implemented. Are
multi-column indexes implemented as true multiple level indexes, in the
sense there is a level 1 index on , pointing to a level 2 index
on , pointing to a level 3 index on ? Or are they the
equivalent of a single index (ie, as if I
created a functional index consisting of
host_luid || ',' || log_luid || ',' || luid
)?
My initial guess was that Postgresql would search first to the
desc, then from there to the specific desc, and then from there
to the (ie, the equivalent of 3 btree jumps), essentialy skipping
over the inappropriate 's of 5 and 4. But it seems to be
scanning through them, even though I have a low cost for random page
accesses within my postgresql.conf file. Are they components of the index
to allow it to "skip" backwards lots of pages rather than loading them
from disk?
Any ideas? How does multi-column indexes really work? I would hate to have
to define specific indexes for each as this is an
unmaintainable situation.
Thanks!
Ed
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Enough RAM for entire Database.. cost aside, is this
Greetings! On Fri, 2 Jul 2004, Mike Rylander wrote: > I find that experience does not bear this out. There is a saying a coworker > of mine has about apps that try to solve problems, in this case caching, > that are well understood and generally handled well at other levels of the > "software stack"... he calls them "too smart by half" :) But on the other hand, general algorithms which are designed to work under a wide variety of circumstances may fail in specific cases. I am thinking of VACUUM which would kill most caching algorithms simply because we cannot tell the O/S "by the by, this set of pages will not be used again, and therefore it would be fine to use almost none of the general cache to store this". All algorithms have assumptions of value distribution and usages. Caches depend on locality of reference, and we do not have an easy way to say when this is broken. Regards! Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Unable to use index?
Hi folks!
A query I am running does not seem to use indexes that are available
(running version 7.4.2). I have the following table:
=> \d replicated
Table "public.replicated"
Column | Type |
Modifiers
-+--+-
rep_id | bigint | not null default
nextval('replicated_id_seq'::text)
rep_component | character varying(100) |
rep_key1| integer |
rep_key2| bigint |
rep_key3| smallint |
rep_replicated | timestamp with time zone |
rep_remotekey1 | integer |
rep_remotekey2 | bigint |
rep_remotekey3 | smallint |
rep_key2b | bigint |
rep_remotekey2b | bigint |
rep_key4| text |
Indexes:
"replicated_pkey" primary key, btree (rep_id)
"replicate_key1_idx" btree (rep_key1, rep_key2, rep_key3)
"replicated_item2_idx" btree (rep_component, rep_key2, rep_key3)
"replicated_item_idx" btree (rep_component, rep_key1, rep_key2, rep_key3)
"replicated_key2_idx" btree (rep_key2, rep_key3)
"replicated_key4_idx" btree (rep_key4)
=> analyze verbose replicated;
INFO: analyzing "public.replicated"
INFO: "replicated": 362140 pages, 3 rows sampled, 45953418 estimated
total rows
ANALYZE
The following does not use an index, even though two are available for the
specific selection of rep_component.
=> explain analyze select * from replicated where rep_component = 'ps_probe' limit 1;
QUERY PLAN
---
Limit (cost=0.00..0.23 rows=1 width=101) (actual time=34401.857..34401.859 rows=1
loops=1)
-> Seq Scan on replicated (cost=0.00..936557.70 rows=4114363 width=101) (actual
time=34401.849..34401.849 rows=1 loops=1)
Filter: ((rep_component)::text = 'ps_probe'::text)
Total runtime: 34401.925 ms
(4 rows)
Yet, if I do the following, an index will be used, and it runs much
faster (even when I swapped the order of the execution).
=> explain analyze select * from replicated where rep_component = 'ps_probe' order by
rep_component limit 1;
QUERY PLAN
---
Limit (cost=0.00..1.66 rows=1 width=101) (actual time=51.163..51.165 rows=1 loops=1)
-> Index Scan using replicated_item2_idx on replicated (cost=0.00..6838123.76
rows=4114363 width=101) (actual time=51.157..51.157 rows=1 loops=1)
Index Cond: ((rep_component)::text = 'ps_probe'::text)
Total runtime: 51.265 ms
(4 rows)
Any reason why the index is not chosen? Maybe I need to up the number of
rows sampled for statistics?
Regards!
Ed
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SET within a function?
I guess it comes back to the semantics of NULL. As has been pointed out in many a database course, what we mean by NULL changes, and how we want to use NULL changes on circumstances. Normally, when I am comparing rows, I do want NULL <> NULL. In this specific instance, no value has been assigned to the specific column for this row, so NULL is appropriate. However, there are cases where I am trying to explicitely test for existence of a specific row in the table, and in this case, I _do_ want a NULL == NULL type of comparison. I could try and specify a dummy value (in this case, I could put in -1), but then I am trying to create a second class of NULLs, and this is usually not considered good design. Note that as a prime example of how postgresql itself is not "consistent" (in the strictest sense) is GROUP BY which treats NULL == NULL (interesting side bar, is there a way to cause GROUP BY to treat NULLs as not equal to each other?). In a theoretical question, how is this justified if NULL should not equal to NULL (other than "it is in the spec")? Also, is there a particular reason for not having a strict equality operator (or is it simply because it is not in the specification)? Performance? No support from the back-end? Something else? Regards, Ed On Wed, 15 Oct 2003, Tom Lane wrote: > Edmund Dengler <[EMAIL PROTECTED]> writes: > > ... I have no real choice in this as there is no way to specify that > > NULL == NULL. > > The conventional wisdom on this is that if you think you need NULL == > NULL to yield true, then you are misusing NULL, and you'd better > reconsider your data representation. The standard semantics for NULL > really do not support any other interpretation of NULL than "I don't > know what this value is". If you are trying to use NULL to mean > something else, you will face nothing but misery. Choose another > representation for whatever you do mean. > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SET within a function?
The problem I would face is that this still needs to be a sequential scan in the table rather than an index lookup. Regards, Ed On Tue, 14 Oct 2003, Arthur Ward wrote: > > Is the rewrite only for the literal 'X = NULL' or will it do a test > > against a value such as 'X = OLD.X' (and rewrite is OLD.X is NULL)? > > > > Is there any way to match NULLS to each other (as I am looking for a > > literal row, not using NULL as the UNKNOWN). I suppose I could put in a > > dummy value for the 'Not a valid value', but it seems to be quite awkward > > when I really do want the NULL. > > I ended up writing an "equivalent" function for the project I'm working > on. It goes like this in plpgsql: > > IF $1 IS NULL THEN > RETURN $2 IS NULL; > ELSIF $2 IS NULL THEN > -- We already know $1 is not null. > RETURN FALSE; > ELSE > -- Both args are not null. > RETURN $1 = $2; > END IF; > > That's the basic idea. I put a wrapper around this to generate a copy of > it for all the data types used in my database. > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SET within a function?
Is the rewrite only for the literal 'X = NULL' or will it do a test against a value such as 'X = OLD.X' (and rewrite is OLD.X is NULL)? Is there any way to match NULLS to each other (as I am looking for a literal row, not using NULL as the UNKNOWN). I suppose I could put in a dummy value for the 'Not a valid value', but it seems to be quite awkward when I really do want the NULL. Regards! Ed On Mon, 13 Oct 2003, Bruno Wolff III wrote: > On Mon, Oct 13, 2003 at 21:16:33 -0400, > Edmund Dengler <[EMAIL PROTECTED]> wrote: > > > > I think if I could do a 'SET TRANSFORM_NULL_EQUALS TO ON' then this might > > fix the issue (don't know, haven't tried it yet). My question is: can this > > be done within a function such that at the end of the function, the value > > is reset back to value upon entering (kind of like 'SET LOCAL' except for > > just the length of the function call). Is this possible? > > I don't think that will do what you want. That setting is used to > rewrite = null as is null, not to change things so that nulls match each > other. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] SET within a function?
Hi all! I am doing some trigger functions that need to find a tuple in another table. The problem is that this second table is doing some summarization work, and I need nulls to equal each other. Basically, in the trigger I do a: SELECT INTO ... x FROM table1 WHERE ...(some straightforward x = old.x)... AND (x1 = old.x1 OR (x1 is null and old.x1 is null)) AND (x2 = old.x2 OR (x2 is null and old.x2 is null)) AND (x3 = old.x3 OR (x3 is null and old.x3 is null)); The problem is that an index is used to perform the straightforward stuff, and then the x1,x2,x3 is done via an index scan, rather than directly. Unfortunately for the data set I have, it can be clustered pretty badly around the straightforward stuff, and so the scan can take multiple seconds per call. I think if I could do a 'SET TRANSFORM_NULL_EQUALS TO ON' then this might fix the issue (don't know, haven't tried it yet). My question is: can this be done within a function such that at the end of the function, the value is reset back to value upon entering (kind of like 'SET LOCAL' except for just the length of the function call). Is this possible? Thanks! Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Buglist
What I am pointing out is that this is all the same issue, and that solutions to the "we can't do priorities because of locking issues" have existed for many years. I/O is the same as processors, it is a resource that needs managing. So the intelligence can be made to exist, it just needs to be made. Now onto other questions: can vacuuming be done without locks? Can it be done in parts (ie, lock only a bit)? Can the I/O be better managed? Is this a general model that would work well? I have plenty of queries that I would love to run on a "as the system allows" basis, or on a "keep a bit of spare cycles or I/O for the important stuff", but which I cannot specify. So a vote from me for any mechanism that allows priorities to be specified. If this is a desired feature, then comes the hard part of what is feasible, what can be done in a reasonable amount of time, and of doing it. Regards! Ed On Thu, 21 Aug 2003, Andrew Sullivan wrote: > On Thu, Aug 21, 2003 at 12:05:28PM -0400, Edmund Dengler wrote: > > Well, if they are locked waiting on vacuum, then vacuum should upgrade > > it's priority to the highest waiting process (priority inheritance). > > This way, vacuum will be running at a priority level equivalent to who is > > waiting on it. > > Right, but all that intelligence is something that isn't in there > now. And anyway, the real issue is I/O, not processor. > > A > > -- > > Andrew Sullivan 204-4141 Yonge Street > Liberty RMS Toronto, Ontario Canada > <[EMAIL PROTECTED]> M2P 2A8 > +1 416 646 3304 x110 > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Bulk Insert / Update / Delete
Wasn't there a feature in some SQL database which was the equivalent of UPDATE OR INSERT ... based on the primary key? Would this accomplish what you want (I know that I have a desire for this feature a couple of times, as I simply have code or triggers to essentially do the equivalent)? Is this a desirable feature for Postgresql? Regards, Ed On Thu, 21 Aug 2003, Philip Boonzaaier wrote: > Hi Ron > > That is just the point. If Postgres cannot tell me which records exist and > need updating, and which do not and need inserting, then what can ? > > In the old world of indexed ISAM files it is very simple - try to get the > record ( row ) by primary key. If it is there, update it, if it is not, > insert it. > > Now, one can do this with a higher level language and SQL combined, but is > SQL that weak ? > > What happens when you merge two tables ? Surely SQL must somehow determine > what needs INSERTING and what needs UPDATING Or does one try to merge, > get a failure, an resort to writing something in Perl or C ? > > Please help to un - confuse me ! > > Regards > > Phil > - Original Message - > From: Ron Johnson <[EMAIL PROTECTED]> > To: PgSQL General ML <[EMAIL PROTECTED]> > Sent: Tuesday, August 19, 2003 6:45 PM > Subject: Re: [GENERAL] Bulk Insert / Update / Delete > > > On Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote: > > Hi Jason > > > > Thanks for your prompt response. > > > > I'm pretty new to SQL, so please excuse the following rather stupid > question > > : > > > > How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible, > > using your suggestion, to simply put in two SQL statements, in the same > > query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to > accomplist > > this in one go ? > > > > Regards > > > > Phil > > How will you which records were updated, thus able to know which need > to be inserted? > > A temporary table and pl/pgsql should do the trick. > > > - Original Message - > > From: Jason Godden <[EMAIL PROTECTED]> > > To: Philip Boonzaaier <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Tuesday, August 19, 2003 4:42 PM > > Subject: Re: [GENERAL] Bulk Insert / Update / Delete > > > > > > Hi Philip, > > > > Pg is more ansi compliant than most (GoodThing (TM)). You can use the > > 'when' > > conditional but not to do what you need. If I understand you correclty > you > > should be able to acheive the same result using two seperate queries and > the > > (NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine > docs > > on pl/pgsql and other postgresql procedural languages which allow you to > use > > loops and conditional statements like 'if'. > > > > Rgds, > > > > J > > > > On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote: > > > I want to be able to generate SQL statements that will go through a list > > of > > > data, effectively row by row, enquire on the database if this exists in > > the > > > selected table- If it exists, then the colums must be UPDATED, if not, > > they > > > must be INSERTED. > > > > > > Logically then, I would like to SELECT * FROM > > > WHERE , and then IF FOUND > > > UPDATE SET ELSE > > > INSERT INTO VALUES > > > END IF; > > > > > > The IF statement gets rejected by the parser. So it would appear that > > > PostgreSQL does not support an IF in this type of query, or maybe not at > > > all. > > > > > > Does anyone have any suggestions as to how I can achieve this ? > > -- > - > Ron Johnson, Jr. [EMAIL PROTECTED] > Jefferson, LA USA > > 484,246 sq mi are needed for 6 billion people to live, 4 persons > per lot, in lots that are 60'x150'. > That is ~ California, Texas and Missouri. > Alternatively, France, Spain and The United Kingdom. > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > This message is privileged and confidential and intended for the addressee only. If > you are not the intended recipient you may not disclose, copy or > in any way use or publish the content hereof, which is subject to copyright.If you > have received this in error, please destroy the original message > and contact us at [EMAIL PROTECTED] Any views expressed in this message > are those of the individual sender, except where the sender specifically > states them to be the view of Computerkit Retail Systems, its subsidiaries or > associates. Please note that the recipient must scan this e-mail and attachments for > viruses. We accept no liability of whatever nature for any loss, > liability,damage or expense resulting directly or indirectly from this transmission > of this message and/or attachments. > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe comma
Re: [GENERAL] Buglist
Well, if they are locked waiting on vacuum, then vacuum should upgrade it's priority to the highest waiting process (priority inheritance). This way, vacuum will be running at a priority level equivalent to who is waiting on it. Regards, Ed On Thu, 21 Aug 2003, Andrew Sullivan wrote: > On Wed, Aug 20, 2003 at 11:41:41PM +0200, Karsten Hilbert wrote: > > You mean, like, "nice 19" or so ? > > ISTR someone reporting problems with locking on the performance list > from doing exactly that. The problem is that the vacuum back end > might take a lock and then not get any processor time -- in which > case everybody else gets their processor slice but can't do anything, > because they have to wait until the niced vacuum process gets back in > line. > > A > > -- > > Andrew Sullivan 204-4141 Yonge Street > Liberty RMS Toronto, Ontario Canada > <[EMAIL PROTECTED]> M2P 2A8 > +1 416 646 3304 x110 > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Buglist
What about the use of priority inheritance to deal with the issue of priority inversion (a standard methodology within the real-time world)? Then we could have priorities, but still have low priority processes bumped up if a high level one is waiting on them. Regards, Ed On Wed, 20 Aug 2003, Tom Lane wrote: > Andrew Sullivan <[EMAIL PROTECTED]> writes: > >> I disagree. Triggering a vacuum on a db that is nearly saturating the > >> disk bandwidth has a significant impact. > > > Vivek is right about this. If your system is already very busy, then > > a vacuum on a largish table is painful. > > > I don't actually think having the process done in real time will > > help, though -- it seems to me what would be more useful is an even > > lazier vacuum: something that could be told "clean up as cycles are > > available, but make sure you stay out of the way." Of course, that's > > easy to say glibly, and mighty hard to do, I expect. > > I'd love to be able to do that, but I can't think of a good way. > > Just nice'ing the VACUUM process is likely to be counterproductive > because of locking issues (priority inversion). Though if anyone cares > to try it on a heavily-loaded system, I'd be interested to hear the > results... > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Unused Indexes
You need to convert the int's to bigints. select id where col1 = 1::bigint and col2 = 1::bigint Regards, Ed -Original Message- From: Tim McAuley <[EMAIL PROTECTED]> Date: Wed, 30 Jul 2003 13:46:46 To:[EMAIL PROTECTED] Subject: [GENERAL] Unused Indexes Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
