|Pathologically Eclectic Rubbish Lister|
Re^4: Inserting Hash Information Into MySQL (PostgreSQL's hstore)by erix (Vicar)
|on Jun 23, 2012 at 21:40 UTC||Need Help??|
(PostgreSQL:) Once you have a table 't' with a column 'hs' of type hstore, you can use statements like:
to do 'hash-lookups' (but there are many more hstore operators).
Below is a test program in bash/sql. It creates a table with an hstore with a million entries and indexes it.
Then a few searches to show the retrieval performance; same search repeated three times without index and then repeated three times with index:
You see that the speedup is quite considerable :)
Here is that create_hstore.sh program:
(You must have hstore installed - see 'contrib' in older postgres or extensions in newer Pg's)
Obviously we're far away from standard SQL here. Also from the OP's question, I think, but since you expressed interest I thought I'd construct an example. As always, there is much more in the Fine Manual: http://www.postgresql.org/docs/current/static/hstore.html.
(run on a pretty average 8120 desktop, slow single SATA disk, Centos 6.2 Linux, postgresql 9.3devel)
update: Changed to /dev/null redirection with \o inside a psql session. Also changed Timings from EXPLAIN ANALYZE to those from psql's '\timing on' (so I'm showing the slowest);