On 3rd of November, Heikki Linnakangas committed patch:
Support range data types. Selectivity estimation functions are missing for some range type operators, which is a TODO. Jeff Davis
Tag: postgresql
Waiting for 9.2 – Index only scans
On 8th of October, Tom Lane committed patch:
Support index-only scans using the visibility map to avoid heap fetches. When a btree index contains all columns required by the query, and the visibility map shows that all tuples on a target heap page are visible-to-all, we don't need to fetch that heap page. This patch depends on the previous patches that made the visibility map reliable. There's a fair amount left to do here, notably trying to figure out a less chintzy way of estimating the cost of an index-only scan, but the core functionality seems ready to commit. Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas.
What index to create?
Some time ago I wrote a blogpost about why index might not be used.
While this post seemed to be well received (top link from depesz.com on reddit), it doesn't answer another question – what index to create for given situation.
I'll try to cover this question now.
IMPORTANT UPDATE: As of PostgreSQL 10 hash indexes are WAL logged. As such, main point against them is gone.
Waiting for 9.2 – filtered info in explain analyze
On 22nd of September, Tom Lane committed patch:
Make EXPLAIN ANALYZE report the numbers of rows rejected by filter steps. This provides information about the numbers of tuples that were visited but not returned by table scans, as well as the numbers of join tuples that were considered and discarded within a join plan node. There is still some discussion going on about the best way to report counts for outer-join situations, but I think most of what's in the patch would not change if we revise that, so I'm going to go ahead and commit it as-is. Documentation changes to follow (they weren't in the submitted patch either). Marko Tiikkaja, reviewed by Marc Cousin, somewhat revised by Tom
Continue reading Waiting for 9.2 – filtered info in explain analyze
New project – Curo
I'd like to announce start of new project for PostgreSQL admins – Curo.
Waiting for 9.2 – cascading streaming replication
On 19th of July, Simon Riggs committed patch:
Cascading replication feature for streaming log-based replication. Standby servers can now have WALSender processes, which can work with either WALReceiver or archive_commands to pass data. Fully updated docs, including new conceptual terms of sending server, upstream and downstream servers. WALSenders terminated when promote to master. Fujii Masao, review, rework and doc rewrite by Simon Riggs
Continue reading Waiting for 9.2 – cascading streaming replication
Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL
On 18th of July, Tom Lane committed patch:
Add GET STACKED DIAGNOSTICS plpgsql command to retrieve exception info. This is more SQL-spec-compliant, more easily extensible, and better performing than the old method of inventing special variables. Pavel Stehule, reviewed by Shigeru Hanada and David Wheeler
Continue reading Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL
Waiting for 9.2 – Avoiding reindexing on alter type of indexed column
On 18th of July, Robert Haas committed patch:
Avoid index rebuild for no-rewrite ALTER TABLE .. ALTER TYPE. Noah Misch. Review and minor cosmetic changes by me.
Continue reading Waiting for 9.2 – Avoiding reindexing on alter type of indexed column
Write Ahead Log + Understanding postgresql.conf: checkpoint_segments, checkpoint_timeout, checkpoint_warning
explain.depesz.com update
Just updated explain.depesz.com site with new functionality – when showing parsed plan, there is new tab “stats". In there you can see some basic statistics of the query, used types of nodes, and tables that were used by it.
There are probably some problems with stats (it's new functionality, so bugs are imminent), but
As usual, some stats need to be added 🙂
- Total count of plans in database: 18127
- Count of private (not listed on history page) plans: 6965
- Count of anonymized plans: 166 (this feature proved to be much less used than I thought it will be)
- Since (including) May 2011, there are more private plans added than public ones. In June – 58.9% of all plans were private.