[SQL] Constraint on multicolumn index
Hi, I am not sure this can be done but I'm trying to constrain a sorted set efficiently using a multicolumn index in postgres. The (simplified) scenario is this: CREATE TABLE T ( a INT, b INT, c INT ); CREATE INDEX t_idx ON T(a,b,c); Now I can sort using t_idx: select * from T order by a,b,c; -- all good, seq scan using t_idx I can constrain on a single variable fine: select * from T where (a=10 AND b=100 AND c>1000) order by a,b,c; -- does seq scan on t_idx and uses the index in the constraint as expected But if I want the next item following t=(a=10,b=100,c=1000): select * from T where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10) order by a,b,c; then it just does an ordinary filter, and basically does a sequence scan with no intelligence which isn't great if you've got a table of 20 million items. Is there any way short of issuing 3 queries and joining them that I can do this? I had hoped to be able to compare (a,b,c)>(10,100,1000) but of course that evaluates to (a>10) and (b>100) and (c>1000). It feels like there should be a simple solution to this... please help :) Thanks Stuart ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Constraint on multicolumn index
> > But if I want the next item following t=(a=10,b=100,c=1000): > > > select * from T > > where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10) > > order by a,b,c; > > The correct way to handle this is to use a SQL-spec row comparison: > > where (a,b,c) > (10,100,1000) > > Unfortunately, that syntax does not work per-spec in any existing > Postgres release. It will work properly (and use the index) in > PG 8.2, for what that's worth. > > Not sure if there's any reasonable workaround in PG <= 8.1. > You might want to check this old thread: > http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php > (note that none of the first few responses got the point :-() Also > http://archives.postgresql.org/pgsql-hackers/2006-02/msg00209.php Thanks for the response. PG 8.2 looks like a good option when it is finalized (I see it is beta 3 so shouldn't be too long should it?), but I also need to have a closer look at the row constructor - this is a new one for me as I have been using MySQL up til now. The best solution I could come up with for my problem was to do a union - something like: (SELECT * from T WHERE (a=10 AND b=100 AND c>1000) ORDER BY a,b,c LIMIT 10) UNION (SELECT * from T WHERE (a=10 AND b>100) ORDER BY a,b,c LIMIT 10) UNION (SELECT * from T WHERE (a>10) ORDER BY a,b,c LIMIT 10) ORDER BY a,b,c LIMIT 10; which would use an index for each of the selects and then have to merge, sort and limit the results. This seemed to work although it gets clumsy if there are a whole lot of extra criteria. Thanks again for the help, Stuart ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] TIMESTAMP comparison problem
I have a problem in comparing a TIMESTAMP field with a timestamp
literal. I presume it is to do with the floating point representation of
the timestamp but I was wondering if there is an easy work around
without having to recompile postgres to use integer datetimes.
Basically if I issue a "SELECT * FROM T WHERE tstamp>'x';" I get
x as my first field.
If I reduce the precision to 3 for the timestamps it appears to work
although it makes me nervous.
I am running postgresql 8.2.5 on NetBSD 3.
Should I just recompile to use integer datetimes? I would like to have
at least microsecond precision.
Thanks
Stuart
Table definition:
db=> \d+ Transactions;
Table "test.transactions"
Column | Type |
Modifiers transaction_key | bigint
| not null default
nextval('transactions_transaction_key_seq'::regclass) |
time| timestamp(6) without time zone | not null
Indexes:
"transactions_pkey" PRIMARY KEY, btree (transaction_key)
"transactions_time_index" btree ("time", transaction_key)
Has OIDs: no
Table contents:
db=> select transaction_key,time from Transactions;
transaction_key |time
-+
1 | 2008-01-22 09:33:34.681693
2 | 2008-01-22 09:33:34.98421
3 | 2008-01-22 09:33:36.270745
4 | 2008-01-22 09:33:38.573363
5 | 2008-01-22 09:33:38.496988
6 | 2008-01-22 09:33:39.995707
7 | 2008-01-22 09:33:40.111784
8 | 2008-01-22 09:33:41.415505
9 | 2008-01-22 09:33:42.328298
10 | 2008-01-22 09:33:42.025126
11 | 2008-01-22 09:33:44.802205
12 | 2008-01-22 09:33:45.257675
13 | 2008-01-22 09:33:46.746349
14 | 2008-01-22 09:33:46.513937
15 | 2008-01-22 09:33:46.735079
16 | 2008-01-22 09:33:47.528806
17 | 2008-01-22 09:33:49.20255
18 | 2008-01-22 09:33:51.724916
19 | 2008-01-22 09:33:52.550102
20 | 2008-01-22 09:33:54.698312
(20 rows)
Query with problem:
metadb=> select transaction_key,time from Transactions where time>'2008-01-22
09:33:46.746349';
transaction_key |time
-+
13 | 2008-01-22 09:33:46.746349 *** THIS SHOULDN'T BE HERE
16 | 2008-01-22 09:33:47.528806
17 | 2008-01-22 09:33:49.20255
18 | 2008-01-22 09:33:51.724916
19 | 2008-01-22 09:33:52.550102
20 | 2008-01-22 09:33:54.698312
(6 rows)
---(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
Re: [SQL] TIMESTAMP comparison problem
If I reduce the precision to 3 for the timestamps it appears to work although it makes me nervous. With float timestamps, you're fooling yourself if you think those numbers past the decimal are reliable. Should I just recompile to use integer datetimes? I would like to have at least microsecond precision. Well, you can't get better than microsecond precision with timestamps in Postgres. And the only way you can rely on that level of precision is to compile with --enable-integer-datetimes. Michael Glaesemann I thought that might be the case, thanks for the help, Stuart ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Slow GROUP BY query
I have a very simple table set: Transactions: transaction_key PRIMARY KEY client TEXT time TIMESTAMP LineItems transaction_key INT amount INT A query to print the contents of transactions with a sum of the line item amounts provides a very suboptimal result. The problem seems to be the GROUP BY clause as it doesn't use the primary index. Rewriting the query to only group on the transaction_key and returning the max of the other transaction fields results in a query of <1ms. (see queries below) Can anyone shed any light here, I would have expected the queries to take roughly the same time? Out of interest, since we are grouping by transaction_key which is unique, surely the other Transaction fields in the group by could be ignored by the planner? Thanks Stuart (running postgresql 8.2.5 on NetBSD 3) >> Slow query EXPLAIN SELECT t.transaction_key,t.cashier,t.time,SUM(l.amount) FROM Transactions t JOIN LineItems l USING (transaction_key) GROUP BY t.transaction_key,t.cashier,t.time ORDER BY t.transaction_key; QUERY PLAN --- Sort (cost=449.16..454.16 rows=2000 width=32) Sort Key: t.transaction_key -> HashAggregate (cost=314.50..339.50 rows=2000 width=32) -> Hash Join (cost=66.00..262.07 rows=5243 width=32) Hash Cond: (l.transaction_key = t.transaction_key) -> Seq Scan on lineitems l (cost=0.00..117.43 rows=5243 width=16) -> Hash (cost=41.00..41.00 rows=2000 width=24) -> Seq Scan on transactions t (cost=0.00..41.00 rows=2000 width=24) (8 rows) Fast query EXPLAIN SELECT t.transaction_key,MAX(t.cashier),MAX(t.time),SUM(l.amount) FROM Transactions t JOIN LineItems l USING (transaction_key) GROUP BY t.transaction_key ORDER BY t.transaction_key; QUERY PLAN - GroupAggregate (cost=0.00..459.11 rows=2000 width=32) -> Merge Join (cost=0.00..371.68 rows=5243 width=32) Merge Cond: (t.transaction_key = l.transaction_key) -> Index Scan using transactions_pkey on transactions t (cost=0.00..86.25 rows=2000 width=24) -> Index Scan using lineitems_transaction_index on lineitems l (cost=0.00..214.90 rows=5243 width=16) (5 rows) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Am I wasting my time with partitions?
It seems to me that postgresql doesn't use indexes when being asked for an ordered result sets from a partitioned table. I have an application where this is critical, but I was hoping to use partitions because of the ease of rotating out old rows. Simply put, I have a table called LineItems which I need to be able to page from and so I need to be able to ask for N rows ordered on a certain index (with possible constraints). eg. SELECT * FROM T ORDER BY col1,col2 LIMIT 10; This works fine and is quick on a single table: >>> metadb=> \d lineitems Table "test2.lineitems" Column| Type | Modifiers --++--- lineitem_key | bigint | not null time | timestamp(6) without time zone | not null description | text | not null barcode | text | not null amount | bigint | not null Indexes: "lineitems_amount_index" btree (amount, lineitem_key) metadb=> explain select * from lineitems order by amount,lineitem_key limit 10; QUERY PLAN Limit (cost=0.00..0.74 rows=10 width=49) -> Index Scan using lineitems_amount_index on lineitems (cost=0.00..39791.76 rows=535500 width=49) (2 rows) >>> If I partition the table by creating a top level table L, and inherited tables L1, L2 and issue the same request it does sequential scans on all the tables and takes orders of magnitude longer (see below). In the example below I would have hoped that it would have used an index scan on each of the tables returning 10 rows each and then done a merge on them. Am I asking too much? Should I just use a single table and take the hits on deletes and vacuums? Regards Stuart >>> metadb=> \d L Table "test2.l" Column| Type | Modifiers --++--- lineitem_key | bigint | not null time | timestamp(6) without time zone | not null description | text | not null barcode | text | not null amount | bigint | not null Indexes: "l_amount_index" btree (amount, lineitem_key) metadb=> \d L1 Table "test2.l1" Column| Type | Modifiers --++--- lineitem_key | bigint | not null time | timestamp(6) without time zone | not null description | text | not null barcode | text | not null amount | bigint | not null Indexes: "l1_amount_index" btree (amount, lineitem_key) Inherits: l metadb=> \d L2 Table "test2.l2" Column| Type | Modifiers --++--- lineitem_key | bigint | not null time | timestamp(6) without time zone | not null description | text | not null barcode | text | not null amount | bigint | not null Indexes: "l2_amount_index" btree (amount, lineitem_key) Inherits: l metadb=> explain select * from l order by amount,lineitem_key limit 10; QUERY PLAN - Limit (cost=22207.70..22207.72 rows=10 width=88) -> Sort (cost=22207.70..23548.09 rows=536156 width=88) Sort Key: test2.l.amount, test2.l.lineitem_key -> Result (cost=0.00..10621.56 rows=536156 width=88) -> Append (cost=0.00..10621.56 rows=536156 width=88) -> Seq Scan on l (cost=0.00..16.90 rows=690 width=88) -> Seq Scan on l1 l (cost=0.00..4951.00 rows=25 width=49) -> Seq Scan on l2 l (cost=0.00..5653.66 rows=285466 width=49) (8 rows) NB. Just addressing one of the inherited tables works fine. metadb=> explain select * from l1 order by amount,lineitem_key limit 10; QUERY PLAN -- Limit (cost=0.00..0.74 rows=10 width=49) -> Index Scan using l1_amount_index on l1 (cost=0.00..18554.20 rows=25 width=49) (2 rows) >>> ---(end of broadca
Re: [SQL] Am I wasting my time with partitions?
It seems to me that postgresql doesn't use indexes when being asked for an ordered result sets from a partitioned table. I have an application where this is critical, but I was hoping to use partitions because of the ease of rotating out old rows. metadb=> explain select * from l order by amount,lineitem_key limit 10; QUERY PLAN -> Seq Scan on l (cost=0.00..16.90 rows=690 width=88) -> Seq Scan on l1 l (cost=0.00..4951.00 rows=25 width=49) -> Seq Scan on l2 l (cost=0.00..5653.66 rows=285466 width=49) NB. Just addressing one of the inherited tables works fine. metadb=> explain select * from l1 order by amount,lineitem_key limit 10; Well, you don't have an index it can use to find the smallest (amount,lineitem) across all of lX. If PG was smart enough to figure out that it only needed to check l1, then you do. Unfortunately it isn't. You're right, it can't determine which of the partitions will have the smallest value, but what it could do is pull the smallest value from each and compare. In the absence of the LIMIT there wouldn't be much which could be done, but the the LIMIT means it only actually needs to pull 10 rows from each partition. An alternative way of doing this would be: (SELECT * FROM L1 ORDER BY amount,lineitem_key LIMIT 10) UNION (SELECT * FROM L2 ORDER BY amount,lineitem_key LIMIT 10) ORDER BY amount,lineitem_key LIMIT 10; Unfortunately this means one can't just address the parent table, but it does essentially what I'd hoped postgres would do for me :) It would be quite a long query if there were 100 partitions! If you add the constraint you use to partition by, does that help you? I tried to strip the example down to its bare essentials but in this case I would be partitioning by lineitem_key and would obviously index and add a CONSTRAINT on that as well. I don't think it would help though, the query needs to merge from all tables. Thanks for the response, Stuart ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] autovacuum not freeing up unused space on 8.3.0
It appears (and I am open to correction) that autovacuum is not operating correctly in 8.3.0. I have a vanilla installation where autovacuum is enabled, and is running with all the default settings. I have a table which is continually having rows added to it (~50/sec). For the sake of this example I am limiting it to 2 rows, which means that I am continually having to remove rows (100 at a time) as I get to 2. When I get to 2 rows for the first time the table disk size (using pg_total_relation_size) is around 5MB. Since the autovacuum only kicks in after a while I would expect it to get a little bigger (maybe 6-7MB) and then level out as I am cycling through recovered rows. However the table disk size continues increasing basically linearly and when I stopped it it was approaching 40MB and heading up. During that time I was running ANALYZE VERBOSE periodically and I could see the dead rows increase and then drop down as the autovacuum kicked in - the autovacuum worker process was running. It didn't seem to free any space though. In fact a VACUUM FULL at this point didn't help a whole lot either. I ran the same test but using manual VACUUMs every 60 seconds and the table size leveled out at 6.6MB so it appears like a normal vacuum is working. I changed the normal VACUUM to have the same delay parameters (20ms) as the autovacuum and it still worked. So it appears to me like the autovacuum is not freeing up dead rows correctly. I turned on logging for autovacuum and ran the same test and saw the following messages: LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 254 remain tuples: 4082 removed, 19957 remain system usage: CPU 0.02s/0.02u sec elapsed 1.11 sec LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 271 remain tuples: 5045 removed, 19954 remain system usage: CPU 0.03s/0.03u sec elapsed 1.54 sec ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "metadb.test.transactions" At this point I had deleted 32800 rows as can be seen from the query below, although the logs only indicated that around 1 rows had been freed up. select min(transaction_key),max(transaction_key) from test.transactions; min | max ---+--- 32801 | 52750 Is there anything I have missed as far as setting this up is concerned, anything I could try? I would really rather use autovacuum than manage the vacuums of a whole lot of tables by hand... Thanks Stuart PS. Running on NetBSD 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] autovacuum not freeing up unused space on 8.3.0
It seems like pgsql-general would be the right list for this so I am going to post it there rather, sorry for the noise... It appears (and I am open to correction) that autovacuum is not operating correctly in 8.3.0. I have a vanilla installation where autovacuum is enabled, and is running with all the default settings. I have a table which is continually having rows added to it (~50/sec). For the sake of this example I am limiting it to 2 rows, which means that I am continually having to remove rows (100 at a time) as I get to 2. When I get to 2 rows for the first time the table disk size (using pg_total_relation_size) is around 5MB. Since the autovacuum only kicks in after a while I would expect it to get a little bigger (maybe 6-7MB) and then level out as I am cycling through recovered rows. However the table disk size continues increasing basically linearly and when I stopped it it was approaching 40MB and heading up. During that time I was running ANALYZE VERBOSE periodically and I could see the dead rows increase and then drop down as the autovacuum kicked in - the autovacuum worker process was running. It didn't seem to free any space though. In fact a VACUUM FULL at this point didn't help a whole lot either. I ran the same test but using manual VACUUMs every 60 seconds and the table size leveled out at 6.6MB so it appears like a normal vacuum is working. I changed the normal VACUUM to have the same delay parameters (20ms) as the autovacuum and it still worked. So it appears to me like the autovacuum is not freeing up dead rows correctly. I turned on logging for autovacuum and ran the same test and saw the following messages: LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 254 remain tuples: 4082 removed, 19957 remain system usage: CPU 0.02s/0.02u sec elapsed 1.11 sec LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 271 remain tuples: 5045 removed, 19954 remain system usage: CPU 0.03s/0.03u sec elapsed 1.54 sec ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "metadb.test.transactions" At this point I had deleted 32800 rows as can be seen from the query below, although the logs only indicated that around 1 rows had been freed up. select min(transaction_key),max(transaction_key) from test.transactions; min | max ---+--- 32801 | 52750 Is there anything I have missed as far as setting this up is concerned, anything I could try? I would really rather use autovacuum than manage the vacuums of a whole lot of tables by hand... Thanks Stuart PS. Running on NetBSD 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
