[HACKERS] Transitive Closure and 'pg_inherits'
Hi, in case one use 'inherits' relationship to create a hierarchy of tables, table 'pg_inherits' stores for each table the information of which is its parent table. During the evaluation of a query like select * from Root; where Root is the 'root' table of our hierarchy, postgreSQL needs to find which tables are involved in the result (which tables belong to the hierarchy). My question is whether the way, in wich postgresql do this task, is a transitive closure on table 'pg_inherits' or there is a better approach implemented (like numbering scheme techniques etc.) ? If there is a related url, please send it to me. ---(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
[HACKERS] Recursive SQL
Also i'd like to answer you if postgresQL has implemented rcursive queries proposed from SQL99 standard? If yes, are there any restrictions of the model on your implementation? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Raw size
varchar means 'character varying'. What varies is the length. So a
varchar(1000) with 'foo' in it only takes a few bytes ('foo' plus length
info) instead of 1000 bytes.
Yes i know it, but i have vorgotten to inform you that all the values of
this attribute have really 1000 characthers length.
If you really want a fixed-length field, nchar or char should do what you
want.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Raw size
Is there any compression or what? Yes, there is: http://www.postgresql.org/docs/8.0/interactive/storage-toast.html thanks, is there any way to increase the limit, upper wich toast strategy is selected? By defaullt is Block_size/4 = about 2000 Bytes. ---(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
[HACKERS] Explain Analyze mode
Hi, i found this form of output of explain analyze, watching some old mails in lists. test4=# explain analyze select * from patients; LOG: query: explain analyze select * from patients; LOG: duration: 0.603887 sec LOG: QUERY STATISTICS ! system usage stats: ! 0.624269 elapsed 0.458985 user 0.123047 system sec ! [0.468750 user 0.125000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 7/4 [310/158] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 2742 read, 0 written, buffer hit rate = 3.59% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written QUERY PLAN - Seq Scan on patients (cost=0.00..4048.60 rows=131960 width=172) (actual time=0.04..562.97 rows=133886 loops=1) Total runtime: 602.42 msec (2 rows) How can i turn my configuration to achieve this output for explain analyze (and only the QUERY PLAN, as like tomorrow)? ---(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
[HACKERS] Raw size
Hi, i have a table: create table triples( att0 varchar(1000), att1 int4, att2 varchar(20), att3 varchar(1000) ) My table has 990 raws. The (possibly wrong) way, with wich i compute the size of the table is: att0: 1000 * 1 Byte + 4 = 1004 Bytes att2: 20 * 1 Byte + 4 = 24 Bytes att3: 1000 * 1 Byte + 4 = 1004 2032 Bytes + 40 (for oid) = 2072 Bytes 990 * 2072 = 2,051,280 Bytes BUT after clustering triples according to an index on att1: select relname, relpages from pg_class ; relname | relpages -+-- triples | 142 (8KB/buffer) 142 * 8 * 1024 = 1,163,264 Bytes Is there any compression or what? ---(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: [HACKERS] About b-tree usage
let me, i have turned enable_seqscan to off, in order to discourage optimizer to choose seq_scan whenever an idex_scan can be used. But in this case, why optimizer don't chooses seq_scan (discourage is different than prevent) ? At many cases i need only a small fragment of raws to be retrieved. But this extreme case is a real-scenario (not the most frequent but real). I try to find a way to achieve good performence even for the extreme case. Is there any way? ps. In bibliografy, there is a different alternative for indices. except th simple approach of attr_val, rid is the alternative attr_val, set of rids. The second means the attaches to each discrete attr_val the set o rid's of all raws with same attr_val. Is this alternative taken into account in postgres? On Mon, 7 Mar 2005, Jeff Davis wrote: In that case, sequential scan is faster, but perhaps the planner doesn't know that ahead of time. Try turning on more statistics if you haven't already, and then run ANALYZE again. If the planner sees a range, perhaps it assumes that it is a highly selective range, when in fact, it consists of all of the tuples. Also, make sure enable_seqscan is true (in case you turned it off for testing or something and forgot). A seqscan is usually faster when a large proportion of the tuples are returned because: (1) It uses sequential I/O; whereas an index might access tuples in a random order. (2) It doesn't have to read the index's disk pages at all. I suspect you don't need to return all the tuples in the table. If you include the details of a real scenario perhaps the people on the list could be more helpful. ---(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
Re: [HACKERS] About b-tree usage
If there are many identical values in att0, are you sure a sequential scan isn't more efficient? Also, are you sure the index isn't working well? It seems to me since you have the table clustered, it might be fairly efficient as-is (it would get a huge benefit from the spatial locality of the tuples in the table). Index size alone shouldn't destroy your performance, since the idea of an index lookup is that it only has to read O(log n) pages from the disk per lookup. In the next example, have in mind that: select relname, relpages, reltuples from pg_class; relname | relpages | reltuples +--+- ... tc20| 142858 | 1.1e+06 inst_id_idx | 2745 | 1e+06 ... and that i run postgresql, on a UltraSPARC[tm] III 600MHz, ram: 512MB OS : sol 9 att0: varchar(1000) att1: int4 and that 0=att1=9 for every tuple of tabe and index. query: select att0 from tc20 where att1=9 AND att1=0 plan: Index Scan using inst_id_idx on tc20 (cost=0.00..161603.06 rows=106 width=1004) (actual time=41.21..101917.36 rows=100 loops=1) Index Cond: ((att1 = 9) AND (att1 = 0)) Total runtime: 103135.03 msec query: select att0 from tc20 plan: Seq Scan on tc20 (cost=1.00..100152858.06 rows=106 width=1004) (actual time=0.21..42584.87 rows=100 loops=1) Total runtime: 43770.73 msec Can you explain me this big difference? Perhaps postgresql caches in memory a big part (or the whole) of index? And by the way why postgresql doesn't select sequential scan? (I have done vacuum analyze). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] About b-tree usage
Please let me know, if there is any option in postgresql to achieve the following usage of a b-tree index: For a relation R(att0, att1) and a btree index on attribute att0 In each insertion of a tuple on table: - look on index if the value of att0 of new entry does already exist in index, and - if no, allow the aprorpiate entry on b-tree - if yes, do not allow an entry. In my aplication i have always my relation clustered according to att0. And the only information needed for a query with a range condition over att0 in WHERE clause, is the place on disc where the first tuple with a given value on att0 is placed. The hint, is that beacause of too many raws of table, the index size is too big. But the number of discrete values of att0 is orders of magnitudes smaller than the number of tuples. I try to investigate, if there is a way to use an alternative of b-tree index, to decrease the blocks of indexed that are fetched into memory. Thanks. ---(end of broadcast)--- TIP 8: explain analyze is your friend
