Reputation
20,040
Top tag
Next privilege 25,000 Rep.
Access to site analytics
Badges
4 37 71
Newest
 index
Impact
~1.5m people reached

Apr
25
comment Postgresql with just index storage
What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
Apr
25
comment Most efficient way to retrieve data by timestamps
Abelisto is right. Your query only takes 250 milli seconds on the server. If you see 20 seconds as the runtime, then it's the time to either transfer the data over the network (although that seems really long for 20000 rows) or it's your SQL client (or application) that takes 20 seconds to display those rows.
Apr
24
comment The definitive fastest radius search query with Postgis 2.0
Check the execution plans. Also read this: wiki.postgresql.org/wiki/Slow_Query_Questions then edit your question and add the missing information
Apr
22
comment Oracle not able to add Default value in PL/SQL
@BriteSponge: that's what I was referring to with "alternative ways"
Apr
21
comment Get multiple columns from a select subquery
cross apply is available in Postgres starting with 9.3 (released in 2013) but they chose to adhere to the SQL standard and use the standard lateral operator. In your second query replace left join with left join lateral
Apr
18
comment How do I find the day of week of a date-time field in postgres?
TIMESTAMP date is invalid syntax. If you have a column named date remove timestamp. If you have a column named timestamp remove date (another good example why timestamp or date are horrible names for columns)
Apr
17
comment Does Oracle 11g support CREATE ASSERTION?
Actually there is no DBMS that supports assertions
Apr
15
comment select only one element from an priority ordered group with limits?
@lumo: you asked for a DBMS independent solution, not one that works with the exteremly limited SQL features in MySQL. Window functions have been part of the SQL standard for over a decade.
Apr
14
comment How to get one MAX() between SUMs of several columns in SQL?
Which DBMS are you using?
Apr
13
comment Query Last Value in Fixed Length Array With Empty Values
Is location an array column, e.g. text[]?
Apr
12
comment I have a query challenge for anyone willing to participate
Which DBMS are you using?
Apr
10
comment open/execute huge sql file
use psql -f large_file.sql
Apr
8
comment Order by index unexpectedly slowing query down
The cast to ::date is not necessary, but if you include it in the index you should do that in the order by as well. Btw: which Postgres version are you using?
Apr
8
comment Order by index unexpectedly slowing query down
'-infinity' is a (date) value that is smaller than all others. It does work, I just don't know if it's faster
Apr
8
comment Order by index unexpectedly slowing query down
Did you try order by coalesce(houses.mortgage_maturation_date, '-infinity') desc and an index on the coalesce() expression?
Apr
8
comment How to retrieve closest value based on look-up table?
Oracle supports lateral joins since 12c (include Microsoft's keyword using apply) docs.oracle.com/database/121/SQLRF/…
Apr
8
comment Does MySQL updated index if index key is not updated?
But the PK index - being a clustered index and thus contains the complete table data - will be updated, won't it?
Apr
6
comment How to copy a Postgres database within the same DB cluster?
You can use pg_basebackup for that: postgresql.org/docs/current/static/app-pgbasebackup.html
Apr
5
comment is there a max number of rows in a SQL Server 2008/2012 and MySQL table?
3) is impossible to answer because it depends on gazillion variables in your system
Apr
5
comment compare file-based systems and a relational database
Independence of the physical storage location. Independence of the physical storage structure